Sum Values by month from 1st day to last day of each month, and specific date range

[Zakky]

Hello, I have a spreadsheet that shows a day's sales in each column across the top from 1st day of year to the last day (365 columns of data). I am trying to find a dynamic way to sum the values for each product by month. The total for each month should start from 1st day of month to last day of month. As a bonus, would it also be possible to show a solution that would take sales from a specific start and end date, so if i wanted to see what the sales value is from, say 15th Jan to 20 March for a product. Your help with this problem is greatly appreciated. Thank you.

 

[alansidman]

Cannot manipulate data in a picture. Please reload your data using XL2BB

 

[SunnyAlv]

try wityh Sumproduct

**Note : I extended the number of columns to Column "NB" because you said the number of columns is 365

cell B3 and copy to other cells

=SUMPRODUCT((TEXT($B$2:$NB$2,"MMM YY")=TEXT(B$8,"MMM YY"))*($A$3:$A$5=$A9)*($B$3:$NB$5))

cell B15 and copy to other cells

=SUMPRODUCT(($B$2:$NB$2>$B$14)*($B$2:$NB$2<$C$14)*($A$3:$A$5=$A15)*($B$3:$NB$5))

 

[Zakky]

Cannot manipulate data in a picture. Please reload your data using XL2BB

alansidman, thank you for the prompt reply. I can't get the XL2BB to work. I've downloaded it but it's not appearing in add-in. Have a nice day.

 

[Zakky]

try wityh Sumproduct

**Note : I extended the number of columns to Column "NB" because you said the number of columns is 365

cell B3 and copy to other cells

=SUMPRODUCT((TEXT($B$2:$NB$2,"MMM YY")=TEXT(B$8,"MMM YY"))*($A$3:$A$5=$A9)*($B$3:$NB$5))

cell B15 and copy to other cells

=SUMPRODUCT(($B$2:$NB$2>$B$14)*($B$2:$NB$2<$C$14)*($A$3:$A$5=$A15)*($B$3:$NB$5))

View attachment 113926

SunnyAlv, Thank you for the prompt reply. Both of your solutions are magic, beautifully done. Have a nice day.

 

[FormR]

Hi, here's another option you can try that will probably be more performant than the SUMPRODUCT() alternative.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1
2		01/01/2023	02/01/2023	03/01/2023	01/02/2023	02/02/2023	03/02/2023	01/03/2023	02/03/2023	03/03/2023	01/04/2023	02/04/2023	03/04/2023	31/12/2023
3	Product A	100	200	50	500	65	750	18	46	42	19	31	14	49
4	Product B	110	220	55	550	72	825	55	32	79	79	18	63	22
5	Product C	121	242	61	605	79	908	68	95	24	69	97	46	83
6
7
8		01/01/2023	01/02/2023	01/03/2023	01/04/2023	01/05/2023	01/06/2023	01/07/2023	01/08/2023	01/09/2023	01/10/2023	01/11/2023	01/12/2023
9	Product A	350	1315	106	64	0	0	0	0	0	0	0	49
10	Product B	385	1447	166	160	0	0	0	0	0	0	0	22
11	Product C	424	1592	187	212	0	0	0	0	0	0	0	83
12
13
14		15/01/2023	20/03/2023
15	Product A	1421
16	Product B	1613
17	Product C	1779
Sheet1

Cell Formulas
Range	Formula
B9:M11	B9	=SUMIFS(INDEX($B$3:$N$5,MATCH($A9,$A$3:$A$5,0),0),$B$2:$N$2,">="&B$8,$B$2:$N$2,"<"&EOMONTH(B$8,0)+1)
B15:B17	B15	=SUMIFS(INDEX($B$3:$N$5,MATCH($A15,$A$3:$A$5,0),0),$B$2:$N$2,">="&B$14,$B$2:$N$2,"<"&C$14+1)

 

[alansidman]

If you normalize your data in a range/table, then you can easily pivot your data to achieve the expected results. Your data should be in columns and not rows. Long and Skinny versus short and fat.