Sumifs Help

K

kkspm13

New Member
Joined
Aug 2, 2022
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hello! I have a data set that does not include totals. I would like sum the data based on qualifications listed in a drop down menu:
1659479264406.png

This would typically require a standard sumifs, however there are not totals in my data set so if total is selected as one of the drop downs the formula does not work.
What I was trying to do was IFS and using the logical statement to create a parameter based on the filter, followed by the sumifs.
For example: If BTID is set to Total then Total should not be included in the sumifs formula as this would not be found in the criteria range / we would want all BTIDs to be included.

I tried to write this lengthy formula to spell out all of the possible combinations of filters as the logical statements followed by the sumifs excluding whatever filter combination was listed in the logical statement:

IFS(Rank!$H$2="Total",SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),Rank!$H$3="Total",SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!F:F,Rank!$H$2,Data!$D:$D,Rank!H4,Data!$C:$C,Rank!H5),Rank!H4="Total",SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$F:$F,Rank!$H$2,Data!$C:$C,Rank!$H$5,and(H2="Total",H3="Total"),sumif(Data!G:G,Data!$A:$A,Rank!$B8,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),if($H$2="Total",$H$4="Total"),sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$m:$m,Rank!$H$3,Data!$C:$C,Rank!$H$5),and(H3="Total",H4="Total"),sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$f:$f,Rank!$H$2,Data!$C:$C,Rank!$H$5),and(H2="Total",H3="Total",H4="Total"),sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$C:$C,Rank!$H$5))

Ultimately this did not work. I guess what I am wondering is there any formula that would allow me to account for the fact that the sumifs needs to change depending on the filter selection?
 
Sort by date Sort by votes
Peter_SSs said:
Could you please post (or repost) the final formula? (& sample data would also be good since you already have XL2BB working)
Could you please answer the second question from post #16

I'm pretty sure that this can be done with a much simpler & more efficient formula.
Click to expand...
Final Formula:
IFS(Rank!$H$2="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
Rank!$H$3="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!F:F,Rank!$H$2,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
Rank!$H$4="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$F:$F,Rank!$H$2,Data!$C:$C,Rank!$H$5),
Rank!$H$5="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$F:$F,Rank!$H$2,Data!$D:$D,Rank!$H$4),
AND(Rank!$H$2="Total",Rank!$H$3="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$2="Total",Rank!$H$4="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$3="Total",Rank!$H$4="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$F:$F,Rank!$H$2,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$2="Total",Rank!$H$3="Total",Rank!$H$4="Total",Rank!$H$5="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8),TRUE,"")
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Peter_SSs said:
Could you please post (or repost) the final formula? (& sample data would also be good since you already have XL2BB working)
Could you please answer the second question from post #16

I'm pretty sure that this can be done with a much simpler & more efficient formula.
Click to expand...
kkspm13 said:
Final Formula:
IFS(Rank!$H$2="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
Rank!$H$3="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!F:F,Rank!$H$2,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
Rank!$H$4="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$F:$F,Rank!$H$2,Data!$C:$C,Rank!$H$5),
Rank!$H$5="Total",
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$F:$F,Rank!$H$2,Data!$D:$D,Rank!$H$4),
AND(Rank!$H$2="Total",Rank!$H$3="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$2="Total",Rank!$H$4="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$3="Total",Rank!$H$4="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$F:$F,Rank!$H$2,Data!$C:$C,Rank!$H$5),
AND(Rank!$H$2="Total",Rank!$H$3="Total",Rank!$H$4="Total",Rank!$H$5="Total"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8),TRUE,"")
Click to expand...
Peter_SSs said:
Could you please post (or repost) the final formula? (& sample data would also be good since you already have XL2BB working)
Could you please answer the second question from post #16

I'm pretty sure that this can be done with a much simpler & more efficient formula.
Click to expand...
The Sheeet the formula is on is "summary"
 
Upvote 0
kkspm13 said:
The Sheeet the formula is on is "summary"
Click to expand...
Well it is on summary in this mini data set: (in my real file it is on "rank")
Sample Data File.xlsx
ABCDEFGHIJKLMNOP
1BTID -->MATData
2Priority -->PriorityPrioirityDist StatesDistributor Roll UpsPremisePrice TiersBrand TiersBrand Tier ID#s
3LPC -->PremiumPriorityNYMEmpire Merchants - NYMONPremiumDiscontinued PremiumXPR502
4Premise -->ONPriorityNYMEmpire Merchants - NYMONPremiumMatMAT499.0833
5PriorityNYMEmpire Merchants - NYMONPremiumSterSTVC285.5833
6NYU499.0833PriorityNYMEmpire Merchants - NYMONPremiumGabGBPR152.0833
7PriorityNYMEmpire Merchants - NYMONPremiumUppUPP147.1667
8OtherNYMEmpire Merchants - NYMONPremiumAbaABA133
9OtherNYMEmpire Merchants - NYMONPremiumSthTST44
10PriorityNYMEmpire Merchants - NYMONCommercialLindLIN157
11OtherNYMEmpire Merchants - NYMONCommercialMagnoliaMAG0
12OtherNYMEmpire Merchants - NYMONCommercialDiscontinued CommercialXCO0
13OtherNYMEmpire Merchants - NYMONLuxuryFrankFFV92.08333
14PriorityNYMEmpire Merchants - NYMONLuxuryStaSLW80.16667
15PriorityNYMEmpire Merchants - NYMONLuxuryLyricETUL68
16OtherNYMEmpire Merchants - NYMONLuxuryBVNapBVNV57
17PriorityNYMEmpire Merchants - NYMONLuxuryBerBEKV39.58333
18PriorityNYMEmpire Merchants - NYMONLuxurySterSTNV35
19OtherNYMEmpire Merchants - NYMONLuxuryAcaACA24
20OtherNYMEmpire Merchants - NYMONLuxuryEtuETU17.41667
21PriorityNYMEmpire Merchants - NYMOFFPremiumMatMAT467.25
22PriorityNYMEmpire Merchants - NYMOFFPremiumSterSTVC187
23OtherNYMEmpire Merchants - NYMOFFPremiumAbaABA33
24OtherNYMEmpire Merchants - NYMOFFPremiumUppUPP30
25PriorityNYMEmpire Merchants - NYMOFFPremiumSthTST25
26PriorityNYMEmpire Merchants - NYMOFFPremiumGabGBPR7
27OtherNYMEmpire Merchants - NYMOFFPremiumDiscontinued PremiumXPR5
28PriorityNYMEmpire Merchants - NYMOFFLuxuryFrankFFV122
29PriorityNYMEmpire Merchants - NYMOFFLuxuryBerBEKV67.5
30OtherNYMEmpire Merchants - NYMOFFLuxurySterSTNV57
31PriorityNYMEmpire Merchants - NYMOFFLuxuryStaSLW49.5
32PriorityNYMEmpire Merchants - NYMOFFLuxuryBVNapBVNV18
33OtherNYMEmpire Merchants - NYMOFFLuxuryEtuETU13
34OtherNYMEmpire Merchants - NYMOFFLuxuryLyricETUL12
35OtherNYMEmpire Merchants - NYMOFFLuxuryAcaACA11
36OtherNYMEmpire Merchants - NYMOFFCommercialLindLIN65
37
summary
Cell Formulas
RangeFormula
C6C6=SUMIFS(data!H:H,data!D:D,summary!C4,data!E:E,summary!C3,data!F:F,summary!C1,data!A:A,summary!C2)
Cells with Data Validation
CellAllowCriteria
C1List=mapping!$B$3:$B$51
C2List=mapping!$D$3:$D$5
C3List=mapping!$E$3:$E$6
C4List=mapping!$C$3:$C$5
 
Upvote 0
kkspm13 said:
Well it is on summary in this mini data set:
Click to expand...
I can't see the formula on that mini-sheet. For the one on that mini-sheet it would have different references since the drop-downs are in column C, not column H.
Can you post the working formula from that mini-sheet?

I also assume that for the formula on this mini sheet you were using the sample data on this sheet too? That would be good to test my idea with if I can see just what you formula is doing.
 
Upvote 0
OK, I have taken a stab at this. Referring to the sample data provided in post #23, my understanding is that you want to sum column P where columns O, I, M and L match the values in C1:C4 respectively but if anything in C1:C4 is "Total" then you don't want to restrict the relevant column at all. If that is the correct interpretation (🤞), then see if this much shorter formula is any use. (I have hidden some columns not related to the calculations to keep the mini-sheets smaller.)

kkspm13.xlsm
BCDILMOP
1BTID -->MATData
2Priority -->PriorityPrioirityPremisePrice TiersBrand Tier ID#s
3LPC -->PremiumPriorityONPremiumXPR502
4Premise -->ONPriorityONPremiumMAT499.083334
5PriorityONPremiumSTVC285.583334
6PriorityONPremiumGBPR152.083333
7PriorityONPremiumUPP147.166667
8Test Result499.083334OtherONPremiumABA133
9OtherONPremiumTST44
10PriorityONCommercialLIN157
11OtherONCommercialMAG0
12OtherONCommercialXCO0
13OtherONLuxuryFFV92.083333
14PriorityONLuxurySLW80.166666
15PriorityONLuxuryETUL68
16OtherONLuxuryBVNV57
17PriorityONLuxuryBEKV39.583334
18PriorityONLuxurySTNV35
19OtherONLuxuryACA24
20OtherONLuxuryETU17.416667
21PriorityOFFPremiumMAT467.25
22PriorityOFFPremiumSTVC187
23OtherOFFPremiumABA33
24OtherOFFPremiumUPP30
25PriorityOFFPremiumTST25
26PriorityOFFPremiumGBPR7
27OtherOFFPremiumXPR5
28PriorityOFFLuxuryFFV122
29PriorityOFFLuxuryBEKV67.5
30OtherOFFLuxurySTNV57
31PriorityOFFLuxurySLW49.5
32PriorityOFFLuxuryBVNV18
33OtherOFFLuxuryETU13
34OtherOFFLuxuryETUL12
35OtherOFFLuxuryACA11
36OtherOFFCommercialLIN65
Summary
Cell Formulas
RangeFormula
C8C8=LET(x,"Total",T,TRUE,SUM(FILTER(P3:P36,IF(C1=x,T,O3:O36=C1)*IF(C2=x,T,I3:I36=C2)*IF(C3=x,T,M3:M36=C3)*IF(C4=x,T,L3:L36=C4),0)))


In the next example, the result is just a sum of column P since all 'filters' are "Total"

kkspm13.xlsm
BCDILMOP
1BTID -->TotalData
2Priority -->TotalPrioirityPremisePrice TiersBrand Tier ID#s
3LPC -->TotalPriorityONPremiumXPR502
4Premise -->TotalPriorityONPremiumMAT499.083334
5PriorityONPremiumSTVC285.583334
6PriorityONPremiumGBPR152.083333
7PriorityONPremiumUPP147.166667
8Test Result3502.41667OtherONPremiumABA133
9OtherONPremiumTST44
10PriorityONCommercialLIN157
11OtherONCommercialMAG0
12OtherONCommercialXCO0
13OtherONLuxuryFFV92.083333
14PriorityONLuxurySLW80.166666
15PriorityONLuxuryETUL68
16OtherONLuxuryBVNV57
17PriorityONLuxuryBEKV39.583334
18PriorityONLuxurySTNV35
19OtherONLuxuryACA24
20OtherONLuxuryETU17.416667
21PriorityOFFPremiumMAT467.25
22PriorityOFFPremiumSTVC187
23OtherOFFPremiumABA33
24OtherOFFPremiumUPP30
25PriorityOFFPremiumTST25
26PriorityOFFPremiumGBPR7
27OtherOFFPremiumXPR5
28PriorityOFFLuxuryFFV122
29PriorityOFFLuxuryBEKV67.5
30OtherOFFLuxurySTNV57
31PriorityOFFLuxurySLW49.5
32PriorityOFFLuxuryBVNV18
33OtherOFFLuxuryETU13
34OtherOFFLuxuryETUL12
35OtherOFFLuxuryACA11
36OtherOFFCommercialLIN65
Summary
Cell Formulas
RangeFormula
C8C8=LET(x,"Total",T,TRUE,SUM(FILTER(P3:P36,IF(C1=x,T,O3:O36=C1)*IF(C2=x,T,I3:I36=C2)*IF(C3=x,T,M3:M36=C3)*IF(C4=x,T,L3:L36=C4),0)))



One other comment in relation to a lot of the earlier formulas that apparently were on sheet 'Rank': Those formulas used the sheet name in many places.
eg small sample
kkspm13 said:
IFS(Rank!$H$2="Total",SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$M:$M,Rank!$H$3,Data!$D:$D,Rank!$H$4, ...
Click to expand...
Using the sheet name of the sheet a formula is on is a bad idea and can lead to unnoticed errors in results in some circumstances. That is why I kept asking about the sheet name. ;)
You should always try to avoid/remove such sheet references.
 
Upvote 0
Peter_SSs said:
LET(x,"Total",T,TRUE,SUM(FILTER(P3:P36,IF(C1=x,T,O3:O36=C1)*IF(C2=x,T,I3:I36=C2)*IF(C3=x,T,M3:M36=C3)*IF(C4=x,T,L3:L36=C4),0)))
Click to expand...
Hi Peter! Thank you!
I tried using the formula you wrote, however it is returning a name error. I am not sure what I am doing wrong as all the references line up!
1659544505026.png

1659544374989.png
 
Upvote 0
OK, thanks. Perhaps then you have some updates to apply?
However, first see if this resolves the error.
Still for the sample sheet layout & data.

Excel Formula: 
=SUM(FILTER(P3:P36,IF(C1="Total",TRUE,O3:O36=C1)*IF(C2="Total",TRUE,I3:I36=C2)*IF(C3="Total",TRUE,M3:M36=C3)*IF(C4="Total",TRUE,L3:L36=C4),0))
 
Upvote 0
Solution
Peter_SSs said:
OK, thanks. Perhaps then you have some updates to apply?
However, first see if this resolves the error.
Still for the sample sheet layout & data.

Excel Formula: 
=SUM(FILTER(P3:P36,IF(C1="Total",TRUE,O3:O36=C1)*IF(C2="Total",TRUE,I3:I36=C2)*IF(C3="Total",TRUE,M3:M36=C3)*IF(C4="Total",TRUE,L3:L36=C4),0))
Click to expand...
This is incredible!! This is exactly what I was trying to accomplish! Thank you so much! I did not know about the filter function. I guess since there are no duplicate criteria in my data set I did not need sumifs because there was nothing to sum, but rather i needed to extract a specific value. It also seems that filter allows for more criteria than the sumifs like stopping if the cell is = to total eliminating the need for me to try and write a bunch of exceptions! Thank you!
 
Upvote 0
You must log in or register to reply here.

Similar threads

mrcsbenson
  • Locked
  • Question
Updating Ranking Column
Replies
1
Views
50
Fluff
Fluff
H
  • Question
SUMIFS
Replies
2
Views
322
Haree
H
D
  • Question
Need help finding formula to pull then rank rates based on separate column
Replies
2
Views
233
felixstraube
F
A
  • Question
Sumif / vlookup question
Replies
1
Views
154
RobP
R
R
  • Question
Applying a formula to a subset of rows
Replies
3
Views
189
rodqntr
R

Forum statistics

Threads
1,216,022
Messages
6,128,325
Members
449,440
Latest member
Gillian McGovern

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