SUM multiple cells data IF month matches date column

[joshua0]

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:

	01/01/2019	05/01/2019	04/02/2019	21/02/2019	26/02/2019	04/03/2019
Item 1	0	3	6	2	4	6
Item 2	2	1	4	0	2	1
Item 3	1	4	6	1	2	2
Item 4	4	4	0	2	4	4
Item 5	4	2	1	3	1	1
Item 6	0	3	3	2	0	2

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

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

	Jan	Feb	March
Item 1	3	12	6
Item 2	3	6	1
Item 3	5	7	2
Item 4	8	2	4
Item 5	6	4	1
Item 6	3	5	2

<colgroup><col><col><col span="5"></colgroup><tbody>
</tbody>

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

 

[Fluff]

Hi & welcome to MrExcel.

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		01/01/2019	05/01/2019	04/02/2019	21/02/2019	26/02/2019	04/03/2019							Jan	Feb	Mar
2	Item 1	0	3	6	2	4	6						Item 1	3	12	6
3	Item 2	2	1	4	0	2	1						Item 2	3	6	1
4	Item 3	1	4	6	1	2	2						Item 3	5	9	2
5	Item 4	4	4	0	2	4	4						Item 4	8	6	4
6	Item 5	4	2	1	3	1	1						Item 5	6	5	1
7	Item 6	0	3	3	2	0	2						Item 6	3	5	2
Index

Cell Formulas
Range	Formula
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

 

[sandy666]

you can try PowerQuery

item	01/01/2019	05/01/2019	04/02/2019	21/02/2019	26/02/2019	04/03/2019		item	January	February	March
Item 1	0​	3​	6​	2​	4​	6​		Item 1	3​	12​	6​
Item 2	2​	1​	4​	0​	2​	1​		Item 2	3​	6​	1​
Item 3	1​	4​	6​	1​	2​	2​		Item 3	5​	9​	2​
Item 4	4​	4​	0​	2​	4​	4​		Item 4	8​	6​	4​
Item 5	4​	2​	1​	3​	1​	1​		Item 5	6​	5​	1​
Item 6	0​	3​	3​	2​	0​	2​		Item 6	3​	5​	2​

[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]

 

[joshua0]

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)

 

[Fluff]

Glad to help & thanks for the feedback

 

[jtakw]

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)

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1		1/1/2019	1/5/2019	2/4/2019	2/21/2019	2/26/2019	3/4/2019							Jan	Feb	Mar
2	Item 1	0	3	6	2	4	6						Item 1	3	12	6
3	Item 2	2	1	4	0	2	1						Item 2	3	6	1
4	Item 3	1	4	6	1	2	2						Item 3	5	9	2
5	Item 4	4	4	0	2	4	4						Item 4	8	6	4
6	Item 5	4	2	1	3	1	1						Item 5	6	5	1
7	Item 6	0	3	3	2	0	2						Item 6	3	5	2
Sheet319

Cell Formulas
Range	Formula
N2	=SUMPRODUCT((MONTH($B$1:$G$1)=MONTH(N$1))*($A$2:$A$7=$M2)*$B$2:$G$7)

 

[joshua0]

Well spotted, you're absolutely right, I didn't copy it over correctly. Thanks for noticing!