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

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
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
  • Solved
Extract the names of the Items purchased at different prices
Replies
2
Views
134
ala995
A
S
  • Solved
Sum from a table if the key matches a variable array
Replies
3
Views
201
Fluff
Fluff
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
154
krunal123
K
GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
113
Fluff
Fluff
S
  • Question Question
Excluding dates in summing up a range
Replies
3
Views
223
Fluff
Fluff

Forum statistics

Threads
1,223,164
Messages
6,170,444
Members
452,326
Latest member
johnshaji

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