SUM multiple cells data IF month matches date column

J

joshua0

New Member
Joined
Oct 9, 2018
Messages
3
Hi,

I'm having some trouble working out how to return the sum value of an item for any given month.

Each column represents an order and each row a product. The data shows the qty of that product ordered on that date:

[TABLE="width: 750"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]01/01/2019[/TD]
[TD="align: right"]05/01/2019[/TD]
[TD="align: right"]04/02/2019[/TD]
[TD="align: right"]21/02/2019[/TD]
[TD="align: right"]26/02/2019[/TD]
[TD="align: right"]04/03/2019[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


I need to map this to show the qty of each item ordered per month.


[TABLE="width: 750"]
<colgroup><col><col><col span="5"></colgroup><tbody>[TR]
[TD][/TD]
[TD="align: right"]Jan[/TD]
[TD="align: right"]Feb[/TD]
[TD="align: right"]March[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item 4[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]Item 5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[/TR]
</tbody>[/TABLE]


First off, I know I can use pivot tables, but that doesn't really fit in to the rest of what I'm trying to do. In this instance, I need to use regular formulas.

I've been trying to use INDEX MATCH MATCH, but that only wants to return the value of a single cell, not the sum of multiple cells meeting the criteria.

I've also used =SUM(IF(MONTH($B$1:$G$1)=MONTH(B$10),$B$2:$G$7)) to sum all the values in the date range, but I can't figure out how to limit that to a single row/product? I was thinking HLOOKUP, but using a numeric value rather than a dynamic lookupto pick the row is a bit of a limitation.

Anyone have any ideas?

Thanks

Josh
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Sort by date Sort by votes
Hi & welcome to MrExcel.

How about


Excel 2013/2016
ABCDEFGHIJKLMNOP
101/01/201905/01/201904/02/201921/02/201926/02/201904/03/2019JanFebMar
2Item 1036246Item 13126
3Item 2214021Item 2361
4Item 3146122Item 3592
5Item 4440244Item 4864
6Item 5421311Item 5651
7Item 6033202Item 6352
Index
Cell Formulas
RangeFormula
N2=SUMPRODUCT((TEXT($B$1:$G$1,"mmm")=N$1)*($A$2:$A$7=$M2),$B$2:$G$7)
O2=SUMPRODUCT((TEXT($B$1:$G$1,"mmm")=O$1)*($A$2:$A$7=$M2),$B$2:$G$7)
P2=SUMPRODUCT((TEXT($B$1:$G$1,"mmm")=P$1)*($A$2:$A$7=$M2),$B$2:$G$7)


Your output headers will all need to be 3 letter format
 
Upvote 0
you can try PowerQuery

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]item[/td][td=bgcolor:#5B9BD5]01/01/2019[/td][td=bgcolor:#5B9BD5]05/01/2019[/td][td=bgcolor:#5B9BD5]04/02/2019[/td][td=bgcolor:#5B9BD5]21/02/2019[/td][td=bgcolor:#5B9BD5]26/02/2019[/td][td=bgcolor:#5B9BD5]04/03/2019[/td][td][/td][td=bgcolor:#70AD47]item[/td][td=bgcolor:#70AD47]January[/td][td=bgcolor:#70AD47]February[/td][td=bgcolor:#70AD47]March[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 1[/td][td=bgcolor:#DDEBF7]
0​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
6​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
6​
[/td][td][/td][td=bgcolor:#E2EFDA]Item 1[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
12​
[/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 2[/td][td]
2​
[/td][td]
1​
[/td][td]
4​
[/td][td]
0​
[/td][td]
2​
[/td][td]
1​
[/td][td][/td][td]Item 2[/td][td]
3​
[/td][td]
6​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 3[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
6​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]Item 3[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
9​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 4[/td][td]
4​
[/td][td]
4​
[/td][td]
0​
[/td][td]
2​
[/td][td]
4​
[/td][td]
4​
[/td][td][/td][td]Item 4[/td][td]
8​
[/td][td]
6​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Item 5[/td][td=bgcolor:#DDEBF7]
4​
[/td][td=bgcolor:#DDEBF7]
2​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
3​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td=bgcolor:#DDEBF7]
1​
[/td][td][/td][td=bgcolor:#E2EFDA]Item 5[/td][td=bgcolor:#E2EFDA]
6​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Item 6[/td][td]
0​
[/td][td]
3​
[/td][td]
3​
[/td][td]
2​
[/td][td]
0​
[/td][td]
2​
[/td][td][/td][td]Item 6[/td][td]
3​
[/td][td]
5​
[/td][td]
2​
[/td][/tr]
[/table]
Code: 
[SIZE=1]let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"item", type text}, {"01/01/2019", Int64.Type}, {"05/01/2019", Int64.Type}, {"04/02/2019", Int64.Type}, {"21/02/2019", Int64.Type}, {"26/02/2019", Int64.Type}, {"04/03/2019", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"item"}, "Attribute", "Value"),
    #"Changed Type1" = Table.TransformColumnTypes(#"Unpivoted Other Columns",{{"Attribute", type date}}),
    #"Extracted Month Name" = Table.TransformColumns(#"Changed Type1", {{"Attribute", each Date.MonthName(_), type text}}),
    #"Pivoted Column" = Table.Pivot(#"Extracted Month Name", List.Distinct(#"Extracted Month Name"[Attribute]), "Attribute", "Value", List.Sum)
in
    #"Pivoted Column"[/SIZE]
 
Last edited:
Upvote 0
Thanks Fluff, that's perfect!

I put in the months (Jan, Feb, March) as dates (01/01/19, 01/02/19, 01/03/19) then custom formatted the cells as 'mmm' so they show as Jan, Feb, March.

=SUMPRODUCT((MONTH($B$1:$G$1,"mmm")=MONTH(N$1))*($A$2:$A$7=$M2),$B$2:$G$7)
 
Upvote 0
Glad to help & thanks for the feedback
 
Upvote 0
joshua0 said:
Thanks Fluff, that's perfect!

I put in the months (Jan, Feb, March) as dates (01/01/19, 01/02/19, 01/03/19) then custom formatted the cells as 'mmm' so they show as Jan, Feb, March.

=SUMPRODUCT((MONTH($B$1:$G$1,"mmm")=MONTH(N$1))*($A$2:$A$7=$M2),$B$2:$G$7)
Click to expand...

Hi,

Actually I think you have a syntax error (too many arguments) for your formula as posted above.
Also, if N1:P1 are actual dates, you don't need "mmm" within your formula (which is causing the error), so just this will do:


Book1
ABCDEFGHIJKLMNOP
11/1/20191/5/20192/4/20192/21/20192/26/20193/4/2019JanFebMar
2Item 1036246Item 13126
3Item 2214021Item 2361
4Item 3146122Item 3592
5Item 4440244Item 4864
6Item 5421311Item 5651
7Item 6033202Item 6352
Sheet319
Cell Formulas
RangeFormula
N2=SUMPRODUCT((MONTH($B$1:$G$1)=MONTH(N$1))*($A$2:$A$7=$M2)*$B$2:$G$7)
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Multiply an array based on date value entered.
Replies
8
Views
160
Fluff
Fluff
A
  • Locked
  • Question Question
Multiply an array based on date value entered.
Replies
1
Views
56
Fluff
Fluff
I
  • Solved
Date formula not applied the same to all Listbox entries
Replies
2
Views
47
ipbr21054
I
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot
K
  • Solved
Conditionally sum fractional cells based on column headings
Replies
5
Views
160
Cubist
Cubist

Forum statistics

Threads
1,224,820
Messages
6,181,159
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top