Sumifs or Sum product help

K

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1Monthly Sales
3 January Febuary March April May June July August September October November December TOTAL SALES
42012$ 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,1062012
52013$ 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,0742013
62014$ 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,7302014
72015$ 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,6722015
82016$ 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,0582016
92017$ 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,3552017
102018$ 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,7492018
112019$ 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,5792019
122020$ 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,5392020
132021$ 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,7702021
142022$ 8,128,763$ 5,401,750$ 3,765,450$ 12,984,905$ 7,812,055$ 319,975$ 500,800$ 97,500 $ 39,011,1982022
152023 2023
16
17AVERAGE$ 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,985AVERAGE
18
196Yr 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,0326 Yr AVG
202020Vs 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
22Compare Years
2320202015$ (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
272012 dec19-nov20 $ 27,997,346
282013#VALUE! dec18-nov19 $ 35,898,206 Aug-Dec tot
292014 dec17-nov18 $ 40,501,1962018$ 21,210,079
302015 dec16-nov17 $ 29,678,2462017$ 11,888,676
312016 dec15-nov16 $ 34,893,5672016$ 9,426,540
322017 Dec14-Nov15 $ 40,309,6762015$ 16,757,770
332018 AVERAGE $ 34,879,706
342019
352020 Aug-Dec
362021$ 7,477,3472020
372022 $ 17,694,5552019
sales by month
Cell Formulas
RangeFormula
C4:N15C4=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:O15O4=IF(SUM(C4:N4)=0,"",SUM(C4:N4))
P4:P15P4=B4
B5:B15B5=B4+1
C17:O17C17=AVERAGE(C4:C15)
P17P17=+B17
C19:O19C19=AVERAGE(OFFSET(C3,COUNT(C4:C15),0,-$A$19))
P19P19=CONCATENATE(A19, " ",B19)
B20B20=CONCATENATE("Vs"," ",A19," ",B19)
C20C20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,2,FALSE)-C19,"")
D20D20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,3,FALSE)-D19,"")
E20E20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,4,FALSE)-E19,"")
F20F20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,5,FALSE)-F19,"")
G20G20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,6,FALSE)-G19,"")
H20H20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,7,FALSE)-H19,"")
I20I20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,8,FALSE)-I19,"")
J20J20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,9,FALSE)-J19,"")
K20K20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,10,FALSE)-K19,"")
L20L20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,11,FALSE)-L19,"")
M20M20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,12,FALSE)-M19,"")
N20N20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,13,FALSE)-N19,"")
O20O20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,14,FALSE)-O19,"")
P20P20=CONCATENATE("Vs ",A20)
C23C23=VLOOKUP($A$23,$B$4:$O$15,2,FALSE)-VLOOKUP($B$23,$B$4:$O$15,2,FALSE)
D23D23=VLOOKUP($A$23,$B$4:$O$15,3,FALSE)-VLOOKUP($B$23,$B$4:$O$15,3,FALSE)
E23E23=VLOOKUP($A$23,$B$4:$O$15,4,FALSE)-VLOOKUP($B$23,$B$4:$O$15,4,FALSE)
F23F23=VLOOKUP($A$23,$B$4:$O$15,5,FALSE)-VLOOKUP($B$23,$B$4:$O$15,5,FALSE)
G23G23=VLOOKUP($A$23,$B$4:$O$15,6,FALSE)-VLOOKUP($B$23,$B$4:$O$15,6,FALSE)
H23H23=VLOOKUP($A$23,$B$4:$O$15,7,FALSE)-VLOOKUP($B$23,$B$4:$O$15,7,FALSE)
I23I23=VLOOKUP($A$23,$B$4:$O$15,8,FALSE)-VLOOKUP($B$23,$B$4:$O$15,8,FALSE)
J23J23=VLOOKUP($A$23,$B$4:$O$15,9,FALSE)-VLOOKUP($B$23,$B$4:$O$15,9,FALSE)
K23K23=VLOOKUP($A$23,$B$4:$O$15,10,FALSE)-VLOOKUP($B$23,$B$4:$O$15,10,FALSE)
L23L23=VLOOKUP($A$23,$B$4:$O$15,11,FALSE)-VLOOKUP($B$23,$B$4:$O$15,11,FALSE)
M23M23=VLOOKUP($A$23,$B$4:$O$15,12,FALSE)-VLOOKUP($B$23,$B$4:$O$15,12,FALSE)
N23N23=VLOOKUP($A$23,$B$4:$O$15,13,FALSE)-VLOOKUP($B$23,$B$4:$O$15,13,FALSE)
O23O23=VLOOKUP($A$23,$B$4:$O$15,14,FALSE)-VLOOKUP($B$23,$B$4:$O$15,14,FALSE)
B27:B37B27=SEQUENCE(Z3-1,,B4,1)
C28C28=IF(B28="","",VLOOKUP(B28-1,B4:O15,13,FALSE)+SUMIFS(C4:O15,B4:B15,B28))
M29:M32M29=+J8+K8+L8+M8+N8
K27K27=+SUM(C6:M6)+N7
K28:K32K28=+N8+SUM(C7:M7)
K33K33=AVERAGE(K27:K32)
J36:J37J36=SUM(J6:N6)
Dynamic array formulas.
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
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
 
Upvote 0
Solution
etaf said:
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
Click to expand...
Didn’t think about that and was over complicating it thanks!!!
 
Upvote 0
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
ABCDEFGHIJKLM
1JanuaryFebruaryMarchAprilMayJuneJulyAugustSeptemberOctoberNovemberDecember
22008235888174649
32009419929816542
42010898925371261
52011462473328814
62012181973465966
72013156217562423
82014138681759636
92015671614563547
10
11
12November
13201341
Sheet1
Cell Formulas
RangeFormula
B13B13=SUMPRODUCT((COLUMN(B1:M1)-COLUMN(B1)+1<=MATCH(A12,B1:M1,0))*(A2:A9=A13),B2:M9)
 
Upvote 0
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
ABCDEFGHIJKLMNOP
1Monthly Sales
3 January Febuary March April May June July August September October November December TOTAL SALES
42012$ 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,1062012
52013$ 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,0742013
62014$ 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,7302014
72015$ 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,6722015
82016$ 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,0582016
92017$ 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,3552017
102018$ 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,7492018
112019$ 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,5792019
122020$ 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,5392020
132021$ 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,7702021
142022$ 8,128,763$ 5,401,750$ 3,765,450$ 12,984,905$ 7,812,055$ 319,975$ 500,800$ 97,500 $ 39,011,1982022
152023 2023
16
17AVERAGE$ 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,985AVERAGE
18
196Yr 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,0326 Yr AVG
202020Vs 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
22Compare Years
2320202015$ (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
272012$ 17,672,106#VALUE!$ 2,155,059
282013$ 20,651,564#VALUE!$ 8,435,055
292014$ 25,256,205#VALUE!$ 7,477,347
sales by month
Cell Formulas
RangeFormula
C4:N15C4=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:O15O4=IF(SUM(C4:N4)=0,"",SUM(C4:N4))
P4:P15P4=B4
B5:B15B5=B4+1
C17:O17C17=AVERAGE(C4:C15)
P17P17=+B17
C19:O19C19=AVERAGE(OFFSET(C3,COUNT(C4:C15),0,-$A$19))
P19P19=CONCATENATE(A19, " ",B19)
B20B20=CONCATENATE("Vs"," ",A19," ",B19)
C20C20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,2,FALSE)-C19,"")
D20D20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,3,FALSE)-D19,"")
E20E20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,4,FALSE)-E19,"")
F20F20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,5,FALSE)-F19,"")
G20G20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,6,FALSE)-G19,"")
H20H20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,7,FALSE)-H19,"")
I20I20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,8,FALSE)-I19,"")
J20J20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,9,FALSE)-J19,"")
K20K20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,10,FALSE)-K19,"")
L20L20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,11,FALSE)-L19,"")
M20M20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,12,FALSE)-M19,"")
N20N20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,13,FALSE)-N19,"")
O20O20=IFERROR(VLOOKUP($A$20,$B$4:$O$15,14,FALSE)-O19,"")
P20P20=CONCATENATE("Vs ",A20)
C23C23=VLOOKUP($A$23,$B$4:$O$15,2,FALSE)-VLOOKUP($B$23,$B$4:$O$15,2,FALSE)
D23D23=VLOOKUP($A$23,$B$4:$O$15,3,FALSE)-VLOOKUP($B$23,$B$4:$O$15,3,FALSE)
E23E23=VLOOKUP($A$23,$B$4:$O$15,4,FALSE)-VLOOKUP($B$23,$B$4:$O$15,4,FALSE)
F23F23=VLOOKUP($A$23,$B$4:$O$15,5,FALSE)-VLOOKUP($B$23,$B$4:$O$15,5,FALSE)
G23G23=VLOOKUP($A$23,$B$4:$O$15,6,FALSE)-VLOOKUP($B$23,$B$4:$O$15,6,FALSE)
H23H23=VLOOKUP($A$23,$B$4:$O$15,7,FALSE)-VLOOKUP($B$23,$B$4:$O$15,7,FALSE)
I23I23=VLOOKUP($A$23,$B$4:$O$15,8,FALSE)-VLOOKUP($B$23,$B$4:$O$15,8,FALSE)
J23J23=VLOOKUP($A$23,$B$4:$O$15,9,FALSE)-VLOOKUP($B$23,$B$4:$O$15,9,FALSE)
K23K23=VLOOKUP($A$23,$B$4:$O$15,10,FALSE)-VLOOKUP($B$23,$B$4:$O$15,10,FALSE)
L23L23=VLOOKUP($A$23,$B$4:$O$15,11,FALSE)-VLOOKUP($B$23,$B$4:$O$15,11,FALSE)
M23M23=VLOOKUP($A$23,$B$4:$O$15,12,FALSE)-VLOOKUP($B$23,$B$4:$O$15,12,FALSE)
N23N23=VLOOKUP($A$23,$B$4:$O$15,13,FALSE)-VLOOKUP($B$23,$B$4:$O$15,13,FALSE)
O23O23=VLOOKUP($A$23,$B$4:$O$15,14,FALSE)-VLOOKUP($B$23,$B$4:$O$15,14,FALSE)
B27:B37B27=SEQUENCE(Z3-1,,B4,1)
C27C27=O4
D27:D29D27=SUMIFS($J$4:$N$15,$B$4:$B$15,B27)
E27:E29E27=SUM(J4:N4)
C28:C29C28=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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Offset Processes
Replies
7
Views
270
SanjayGMusafir
SanjayGMusafir
gtd526
  • Question
search and answer only ODD rows
Replies
6
Views
142
gtd526
gtd526
P
  • Solved
Update Equally Distributed Formula
2
Replies
19
Views
595
felixstraube
F
M
  • Question
Running unique count of occurrence on table based on date
Replies
0
Views
164
Minx_Minxy
M
E
  • Solved
Summing Values
Replies
3
Views
213
LazyBug
LazyBug

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

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