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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
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

H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
A
  • Solved
Extract the names of the Items purchased at different prices
Replies
2
Views
138
ala995
A
I
  • Solved
Edit working code to look for value after a date & before a date
Replies
6
Views
114
Alex Blakenburg
A
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
210
Fluff
Fluff
A
  • Solved
take amount from last row in listbox on form and put into next column
Replies
7
Views
144
Abdo
A

Forum statistics

Threads
1,223,891
Messages
6,175,229
Members
452,621
Latest member
Laura_PinksBTHFT

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