Sumifs or Sum product help

[keef2]

Hello,

I cannot seem to get the total sum of a row from January to November based on the cell value.
Cell C28 is where i have my attempt but it doesnt seem to work. I have tried Sumproduct as well and get similar errors. Not sure what i am doing wrong. Can someone please help me out here?

Thanks!

masterjoblist - V0.1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Monthly Sales
3			January	Febuary	March	April	May	June	July	August	September	October	November	December	TOTAL SALES
4		2012	$ 217,787	$ 714,700	$ 80,019	$ 7,908,552	$ 3,674,515	$ 522,802	$ 2,398,672	$ 1,251,678	$ 28,843	$ 17,573	$ 9,400	$ 847,565	$ 17,672,106	2012
5		2013	$ 3,093,415	$ 1,090,780	$ 1,623,840	$ 1,796,440	$ 1,717,774	$ 1,044,612	$ 1,825,158	$ 3,733,815	$ 637,388	$ 869,792	$ 2,370,985	$ 823,075	$ 20,627,074	2013
6		2014	$ 2,078,895	$ 2,626,000	$ 6,653,426	$ 36,380	$ 2,859,745	$ 831,390	$ 1,937,547	$ 3,483,615	$ 2,140,838	$ 647,872	$ 1,137,422	$ 67,600	$ 24,500,730	2014
7		2015	$ 2,533,585	$ 2,091,747	$ 4,494,199	$ 1,702,275	$ 1,005,674	$ 2,167,017	$ 7,680,620	$ 110,592	$ 3,045,928	$ 10,878,519	$ 95,300	$ 3,564,216	$ 39,369,672	2015
8		2016	$ 409,465	$ 1,832,825	$ 3,244,407	$ 3,904,514	$ 2,306,938	$ 5,429,274	$ 843,556	$ 8,298,244	$ 4,035,878	$ 1,192,207	$ 7,591,000	$ 92,750	$ 39,181,058	2016
9		2017	$ 3,465,280	$ 1,513,817	$ 6,584,535	$ 879,800	$ 1,218,100	$ 2,192,697	$ 298,450	$ 3,302,787	$ 2,806,055	$ 3,908,009	$ 458,937	$ 1,412,888	$ 28,041,355	2017
10		2018	$ 4,351,558	$ 270,195	$ 2,116,150	$ 5,489,089	$ 2,985,495	$ 2,456,650	$ 8,331,072	$ 2,442,865	$ 3,261,885	$ 367,150	$ 304,861	$ 3,049,779	$ 35,426,749	2018
11		2019	$ 113,838	$ 7,071,370	$ 1,380,520	$ 747,983	$ 5,226,733	$ 1,606,520	$ 7,148,845	$ 1,351,090	$ 792,577	$ 7,278,820	$ 4,818,686	$ 2,516,597	$ 40,053,579	2019
12		2020	$ 1,057,457	$ 5,948,478	$ 2,712,030	$ 1,014,750	$ 1,511,408	$ 1,182,030	$ 4,516,640	$ 3,857,360	$ 2,769,944	$ 1,685,050	$ 1,704,698	$ 2,772,694	$ 30,732,539	2020
13		2021	$ 4,090,350	$ 2,652,478	$ 9,143,516	$ 9,014,203	$ 6,060,405	$ 2,350	$ 8,571,295	$ 11,607,380	$ 535,470	$ 13,199,409	$ 4,606,389	$ 383,525	$ 69,866,770	2021
14		2022	$ 8,128,763	$ 5,401,750	$ 3,765,450	$ 12,984,905	$ 7,812,055	$ 319,975	$ 500,800	$ 97,500					$ 39,011,198	2022
15		2023														2023
16
17		AVERAGE	$ 2,685,490	$ 2,837,649	$ 3,799,827	$ 4,134,445	$ 3,307,167	$ 1,614,120	$ 4,004,787	$ 3,594,266	$ 2,005,481	$ 4,004,440	$ 2,309,768	$ 1,553,069	$ 34,952,985	AVERAGE
18
19	6	Yr AVG	$ 3,534,541	$ 3,809,681	$ 4,283,700	$ 5,021,788	$ 4,135,699	$ 1,293,370	$ 4,894,517	$ 3,776,497	$ 2,366,968	$ 4,605,108	$ 3,247,429	$ 1,704,706	$ 40,522,032	6 Yr AVG
20	2020	Vs 6 Yr AVG	$ (2,477,084)	$ 2,138,797	$ (1,571,670)	$ (4,007,038)	$ (2,624,291)	$ (111,340)	$ (377,877)	$ 80,863	$ 402,976	$ (2,920,058)	$ (1,542,731)	$ 1,067,989	$ (9,789,493)	Vs 2020
21
22	Compare Years
23	2020	2015	$ (1,476,128)	$ 3,856,731	$ (1,782,169)	$ (687,525)	$ 505,734	$ (984,987)	$ (3,163,980)	$ 3,746,768	$ (275,984)	$ (9,193,469)	$ 1,609,398	$ (791,522)	$ (8,637,133)	Compare Yrs
24
25
26		Sale Year Total								sale year:					Sale Year Total
27		2012								dec19-nov20	$ 27,997,346
28		2013	#VALUE!							dec18-nov19	$ 35,898,206		Aug-Dec tot
29		2014								dec17-nov18	$ 40,501,196	2018	$ 21,210,079
30		2015								dec16-nov17	$ 29,678,246	2017	$ 11,888,676
31		2016								dec15-nov16	$ 34,893,567	2016	$ 9,426,540
32		2017								Dec14-Nov15	$ 40,309,676	2015	$ 16,757,770
33		2018								AVERAGE	$ 34,879,706
34		2019
35		2020								Aug-Dec
36		2021								$ 7,477,347	2020
37		2022								$ 17,694,555	2019
sales by month

Cell Formulas
Range	Formula
C4:N15	C4	=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='sales by month'!C$2)*(YEAR(MASTER!$E:$E)='sales by month'!$B4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='sales by month'!C$2)*(YEAR(MASTER!$E:$E)='sales by month'!$B4),MASTER!$G:$G)))))
O4:O15	O4	=IF(SUM(C4:N4)=0,"",SUM(C4:N4))
P4:P15	P4	=B4
B5:B15	B5	=B4+1
C17:O17	C17	=AVERAGE(C4:C15)
P17	P17	=+B17
C19:O19	C19	=AVERAGE(OFFSET(C3,COUNT(C4:C15),0,-$A$19))
P19	P19	=CONCATENATE(A19, " ",B19)
B20	B20	=CONCATENATE("Vs"," ",A19," ",B19)
C20	C20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,2,FALSE)-C19,"")
D20	D20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,3,FALSE)-D19,"")
E20	E20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,4,FALSE)-E19,"")
F20	F20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,5,FALSE)-F19,"")
G20	G20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,6,FALSE)-G19,"")
H20	H20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,7,FALSE)-H19,"")
I20	I20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,8,FALSE)-I19,"")
J20	J20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,9,FALSE)-J19,"")
K20	K20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,10,FALSE)-K19,"")
L20	L20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,11,FALSE)-L19,"")
M20	M20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,12,FALSE)-M19,"")
N20	N20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,13,FALSE)-N19,"")
O20	O20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,14,FALSE)-O19,"")
P20	P20	=CONCATENATE("Vs ",A20)
C23	C23	=VLOOKUP($A$23,$B$4:$O$15,2,FALSE)-VLOOKUP($B$23,$B$4:$O$15,2,FALSE)
D23	D23	=VLOOKUP($A$23,$B$4:$O$15,3,FALSE)-VLOOKUP($B$23,$B$4:$O$15,3,FALSE)
E23	E23	=VLOOKUP($A$23,$B$4:$O$15,4,FALSE)-VLOOKUP($B$23,$B$4:$O$15,4,FALSE)
F23	F23	=VLOOKUP($A$23,$B$4:$O$15,5,FALSE)-VLOOKUP($B$23,$B$4:$O$15,5,FALSE)
G23	G23	=VLOOKUP($A$23,$B$4:$O$15,6,FALSE)-VLOOKUP($B$23,$B$4:$O$15,6,FALSE)
H23	H23	=VLOOKUP($A$23,$B$4:$O$15,7,FALSE)-VLOOKUP($B$23,$B$4:$O$15,7,FALSE)
I23	I23	=VLOOKUP($A$23,$B$4:$O$15,8,FALSE)-VLOOKUP($B$23,$B$4:$O$15,8,FALSE)
J23	J23	=VLOOKUP($A$23,$B$4:$O$15,9,FALSE)-VLOOKUP($B$23,$B$4:$O$15,9,FALSE)
K23	K23	=VLOOKUP($A$23,$B$4:$O$15,10,FALSE)-VLOOKUP($B$23,$B$4:$O$15,10,FALSE)
L23	L23	=VLOOKUP($A$23,$B$4:$O$15,11,FALSE)-VLOOKUP($B$23,$B$4:$O$15,11,FALSE)
M23	M23	=VLOOKUP($A$23,$B$4:$O$15,12,FALSE)-VLOOKUP($B$23,$B$4:$O$15,12,FALSE)
N23	N23	=VLOOKUP($A$23,$B$4:$O$15,13,FALSE)-VLOOKUP($B$23,$B$4:$O$15,13,FALSE)
O23	O23	=VLOOKUP($A$23,$B$4:$O$15,14,FALSE)-VLOOKUP($B$23,$B$4:$O$15,14,FALSE)
B27:B37	B27	=SEQUENCE(Z3-1,,B4,1)
C28	C28	=IF(B28="","",VLOOKUP(B28-1,B4:O15,13,FALSE)+SUMIFS(C4:O15,B4:B15,B28))
M29:M32	M29	=+J8+K8+L8+M8+N8
K27	K27	=+SUM(C6:M6)+N7
K28:K32	K28	=+N8+SUM(C7:M7)
K33	K33	=AVERAGE(K27:K32)
J36:J37	J36	=SUM(J6:N6)
Dynamic array formulas.

 

[etaf]

you have a total in column O and years in P, you could just subtract december
could you just do a lookup

index($O$4:$O$15, match(B27, $P$4:$P$15,0)) - index($N$4:$N$15, match(B27, $P$4:$P$15,0))

i can not copy the sheet into excel - because of all the external links

 

[keef2]

you have a total in column O and years in P, you could just subtract december
could you just do a lookup

index($O$4:$O$15, match(B27, $P$4:$P$15,0)) - index($N$4:$N$15, match(B27, $P$4:$P$15,0))

i can not copy the sheet into excel - because of all the external links

Didn’t think about that and was over complicating it thanks!!!

 

[etaf]

you are welcome

i know you can do it using header and title rows



=SUMPRODUCT((COLUMN(B1:M1)-COLUMN(B1)+1<=MATCH(A12,B1:M1,0))*(A2:A9=A13),B2:M9)

Sumproduct Grid by multiple Header YTD and row titles.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		January	February	March	April	May	June	July	August	September	October	November	December
2	2008	2	3	5	8	8	8	1	7	4	6	4	9
3	2009	4	1	9	9	2	9	8	1	6	5	4	2
4	2010	8	9	8	9	2	5	3	7	1	2	6	1
5	2011	4	6	2	4	7	3	3	2	8	8	1	4
6	2012	1	8	1	9	7	3	4	6	5	9	6	6
7	2013	1	5	6	2	1	7	5	6	2	4	2	3
8	2014	1	3	8	6	8	1	7	5	9	6	3	6
9	2015	6	7	1	6	1	4	5	6	3	5	4	7
10
11
12	November
13	2013	41
Sheet1

Cell Formulas
Range	Formula
B13	B13	=SUMPRODUCT((COLUMN(B1:M1)-COLUMN(B1)+1<=MATCH(A12,B1:M1,0))*(A2:A9=A13),B2:M9)

 

[keef2]

Ok new challenge looking to sum august thru December for the referenced year. Sample non working sumif formula is in Cell D27:29 I just used sumifs to show logic behind what I want to do. Sorry I suck with Sumproduct and I believe this is the function necessary to perform this calc just not sure how to get it to work. Basically hoping for an equation to look up the year and add all values from Aug to December for that year. Thanks in advance!

masterjoblist - V0.1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1			Monthly Sales
3			January	Febuary	March	April	May	June	July	August	September	October	November	December	TOTAL SALES
4		2012	$ 217,787	$ 714,700	$ 80,019	$ 7,908,552	$ 3,674,515	$ 522,802	$ 2,398,672	$ 1,251,678	$ 28,843	$ 17,573	$ 9,400	$ 847,565	$ 17,672,106	2012
5		2013	$ 3,093,415	$ 1,090,780	$ 1,623,840	$ 1,796,440	$ 1,717,774	$ 1,044,612	$ 1,825,158	$ 3,733,815	$ 637,388	$ 869,792	$ 2,370,985	$ 823,075	$ 20,627,074	2013
6		2014	$ 2,078,895	$ 2,626,000	$ 6,653,426	$ 36,380	$ 2,859,745	$ 831,390	$ 1,937,547	$ 3,483,615	$ 2,140,838	$ 647,872	$ 1,137,422	$ 67,600	$ 24,500,730	2014
7		2015	$ 2,533,585	$ 2,091,747	$ 4,494,199	$ 1,702,275	$ 1,005,674	$ 2,167,017	$ 7,680,620	$ 110,592	$ 3,045,928	$ 10,878,519	$ 95,300	$ 3,564,216	$ 39,369,672	2015
8		2016	$ 409,465	$ 1,832,825	$ 3,244,407	$ 3,904,514	$ 2,306,938	$ 5,429,274	$ 843,556	$ 8,298,244	$ 4,035,878	$ 1,192,207	$ 7,591,000	$ 92,750	$ 39,181,058	2016
9		2017	$ 3,465,280	$ 1,513,817	$ 6,584,535	$ 879,800	$ 1,218,100	$ 2,192,697	$ 298,450	$ 3,302,787	$ 2,806,055	$ 3,908,009	$ 458,937	$ 1,412,888	$ 28,041,355	2017
10		2018	$ 4,351,558	$ 270,195	$ 2,116,150	$ 5,489,089	$ 2,985,495	$ 2,456,650	$ 8,331,072	$ 2,442,865	$ 3,261,885	$ 367,150	$ 304,861	$ 3,049,779	$ 35,426,749	2018
11		2019	$ 113,838	$ 7,071,370	$ 1,380,520	$ 747,983	$ 5,226,733	$ 1,606,520	$ 7,148,845	$ 1,351,090	$ 792,577	$ 7,278,820	$ 4,818,686	$ 2,516,597	$ 40,053,579	2019
12		2020	$ 1,057,457	$ 5,948,478	$ 2,712,030	$ 1,014,750	$ 1,511,408	$ 1,182,030	$ 4,516,640	$ 3,857,360	$ 2,769,944	$ 1,685,050	$ 1,704,698	$ 2,772,694	$ 30,732,539	2020
13		2021	$ 4,090,350	$ 2,652,478	$ 9,143,516	$ 9,014,203	$ 6,060,405	$ 2,350	$ 8,571,295	$ 11,607,380	$ 535,470	$ 13,199,409	$ 4,606,389	$ 383,525	$ 69,866,770	2021
14		2022	$ 8,128,763	$ 5,401,750	$ 3,765,450	$ 12,984,905	$ 7,812,055	$ 319,975	$ 500,800	$ 97,500					$ 39,011,198	2022
15		2023														2023
16
17		AVERAGE	$ 2,685,490	$ 2,837,649	$ 3,799,827	$ 4,134,445	$ 3,307,167	$ 1,614,120	$ 4,004,787	$ 3,594,266	$ 2,005,481	$ 4,004,440	$ 2,309,768	$ 1,553,069	$ 34,952,985	AVERAGE
18
19	6	Yr AVG	$ 3,534,541	$ 3,809,681	$ 4,283,700	$ 5,021,788	$ 4,135,699	$ 1,293,370	$ 4,894,517	$ 3,776,497	$ 2,366,968	$ 4,605,108	$ 3,247,429	$ 1,704,706	$ 40,522,032	6 Yr AVG
20	2020	Vs 6 Yr AVG	$ (2,477,084)	$ 2,138,797	$ (1,571,670)	$ (4,007,038)	$ (2,624,291)	$ (111,340)	$ (377,877)	$ 80,863	$ 402,976	$ (2,920,058)	$ (1,542,731)	$ 1,067,989	$ (9,789,493)	Vs 2020
21
22	Compare Years
23	2020	2015	$ (1,476,128)	$ 3,856,731	$ (1,782,169)	$ (687,525)	$ 505,734	$ (984,987)	$ (3,163,980)	$ 3,746,768	$ (275,984)	$ (9,193,469)	$ 1,609,398	$ (791,522)	$ (8,637,133)	Compare Yrs
24
25		Sale Year Total
26		Dec of Prev Year + Year thru Nov		Year Aug - Dec
27		2012	$ 17,672,106	#VALUE!	$ 2,155,059
28		2013	$ 20,651,564	#VALUE!	$ 8,435,055
29		2014	$ 25,256,205	#VALUE!	$ 7,477,347
sales by month

Cell Formulas
Range	Formula
C4:N15	C4	=IF(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='sales by month'!C$2)*(YEAR(MASTER!$E:$E)='sales by month'!$B4),MASTER!$G:$G)))=0,"",(SUM(IF(ISNUMBER(MASTER!$E:$E),IF((MONTH(MASTER!$E:$E)='sales by month'!C$2)*(YEAR(MASTER!$E:$E)='sales by month'!$B4),MASTER!$G:$G)))))
O4:O15	O4	=IF(SUM(C4:N4)=0,"",SUM(C4:N4))
P4:P15	P4	=B4
B5:B15	B5	=B4+1
C17:O17	C17	=AVERAGE(C4:C15)
P17	P17	=+B17
C19:O19	C19	=AVERAGE(OFFSET(C3,COUNT(C4:C15),0,-$A$19))
P19	P19	=CONCATENATE(A19, " ",B19)
B20	B20	=CONCATENATE("Vs"," ",A19," ",B19)
C20	C20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,2,FALSE)-C19,"")
D20	D20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,3,FALSE)-D19,"")
E20	E20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,4,FALSE)-E19,"")
F20	F20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,5,FALSE)-F19,"")
G20	G20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,6,FALSE)-G19,"")
H20	H20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,7,FALSE)-H19,"")
I20	I20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,8,FALSE)-I19,"")
J20	J20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,9,FALSE)-J19,"")
K20	K20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,10,FALSE)-K19,"")
L20	L20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,11,FALSE)-L19,"")
M20	M20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,12,FALSE)-M19,"")
N20	N20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,13,FALSE)-N19,"")
O20	O20	=IFERROR(VLOOKUP($A$20,$B$4:$O$15,14,FALSE)-O19,"")
P20	P20	=CONCATENATE("Vs ",A20)
C23	C23	=VLOOKUP($A$23,$B$4:$O$15,2,FALSE)-VLOOKUP($B$23,$B$4:$O$15,2,FALSE)
D23	D23	=VLOOKUP($A$23,$B$4:$O$15,3,FALSE)-VLOOKUP($B$23,$B$4:$O$15,3,FALSE)
E23	E23	=VLOOKUP($A$23,$B$4:$O$15,4,FALSE)-VLOOKUP($B$23,$B$4:$O$15,4,FALSE)
F23	F23	=VLOOKUP($A$23,$B$4:$O$15,5,FALSE)-VLOOKUP($B$23,$B$4:$O$15,5,FALSE)
G23	G23	=VLOOKUP($A$23,$B$4:$O$15,6,FALSE)-VLOOKUP($B$23,$B$4:$O$15,6,FALSE)
H23	H23	=VLOOKUP($A$23,$B$4:$O$15,7,FALSE)-VLOOKUP($B$23,$B$4:$O$15,7,FALSE)
I23	I23	=VLOOKUP($A$23,$B$4:$O$15,8,FALSE)-VLOOKUP($B$23,$B$4:$O$15,8,FALSE)
J23	J23	=VLOOKUP($A$23,$B$4:$O$15,9,FALSE)-VLOOKUP($B$23,$B$4:$O$15,9,FALSE)
K23	K23	=VLOOKUP($A$23,$B$4:$O$15,10,FALSE)-VLOOKUP($B$23,$B$4:$O$15,10,FALSE)
L23	L23	=VLOOKUP($A$23,$B$4:$O$15,11,FALSE)-VLOOKUP($B$23,$B$4:$O$15,11,FALSE)
M23	M23	=VLOOKUP($A$23,$B$4:$O$15,12,FALSE)-VLOOKUP($B$23,$B$4:$O$15,12,FALSE)
N23	N23	=VLOOKUP($A$23,$B$4:$O$15,13,FALSE)-VLOOKUP($B$23,$B$4:$O$15,13,FALSE)
O23	O23	=VLOOKUP($A$23,$B$4:$O$15,14,FALSE)-VLOOKUP($B$23,$B$4:$O$15,14,FALSE)
B27:B37	B27	=SEQUENCE(Z3-1,,B4,1)
C27	C27	=O4
D27:D29	D27	=SUMIFS($J$4:$N$15,$B$4:$B$15,B27)
E27:E29	E27	=SUM(J4:N4)
C28:C29	C28	=IFERROR(VLOOKUP(B28,$B$4:$O$15,14,FALSE)-VLOOKUP(B28,$B$4:$O$15,13,FALSE)+VLOOKUP(B28-1,$B$4:$O$15,13,FALSE),"")
Dynamic array formulas.

 

[keef2]

I got this figured out thanks