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?
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Sort by date Sort by votes
kkspm13 said:
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:
View attachment 70672
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?
Click to expand...
There are many errors in your formula building. Normally when it's a big or complicated formula, it's better to split that in multiple lines using Alt+Enter. This we can see a formula in small parts and check the intended results.

I tried same with what you have written to understand exactly what you want to do. But after Ninth (9th) line it got so confusing to understand. Moreover what you have written does not match IFS syntax. That I can help you correct. All I need is if you can split your formula in lines they way I have done until 9th line.

Let me understand what you want to do through formula and I can definitely help you correct it.

Excel 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!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))
 
Upvote 0
Welcome to the MrExcel board!

Could you make up a small set of dummy data for each of the worksheets, post those with XL2BB and explain your expected results in relation to that sample data?
 
Upvote 0
SanjayGulatiMusafir said:
There are many errors in your formula building. Normally when it's a big or complicated formula, it's better to split that in multiple lines using Alt+Enter. This we can see a formula in small parts and check the intended results.

I tried same with what you have written to understand exactly what you want to do. But after sixth (6th) line it got so confusing to understand. Moreover what you have written does not match IFS syntax. That I can help you correct. All I need is if you can split your formula in lines they way I have done until 6th line.

Let me understand what you want to do through formula and I can definitely help you correct it.

Excel 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!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))
Click to expand...
Hi Sanjay! Thanks for the reply I have continued the formula starting with line 6:
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)
if(and(Rank!H2="Total",Rank!H3="Total")),
sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
if(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)
if(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)
if(and(Rank!H2="Total",Rank!H3="Total",Rank!H4="Total")),
sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$C:$C,Rank!$H$5)

Essentially trying to do what lines 1-4 do but with multiple parameters prior to doing the sumif instead of just one; which is where the if(and( is coming from trying to string the two parameters together although I do not think that is the correct way to do so.
 
Upvote 0
kkspm13 said:
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:
View attachment 70672
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?
Click to expand...
I think I nailed down what you want by splitting it in lines. Try using this formula underneath

Excel 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!H4,Data!$C:$C,Rank!H5),

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),

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"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$C:$C,Rank!$H$5),True,"")
 
Upvote 0
kkspm13 said:
Hi Sanjay! Thanks for the reply I have continued the formula starting with line 6:
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)
if(and(Rank!H2="Total",Rank!H3="Total")),
sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$D:$D,Rank!$H$4,Data!$C:$C,Rank!$H$5),
if(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)
if(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)
if(and(Rank!H2="Total",Rank!H3="Total",Rank!H4="Total")),
sumifs(Data!G:G,Data!$A:$A,Rank!$B8,Data!$C:$C,Rank!$H$5)

Essentially trying to do what lines 1-4 do but with multiple parameters prior to doing the sumif instead of just one; which is where the if(and( is coming from trying to string the two parameters together although I do not think that is the correct way to do so.
Click to expand...
Please check the formula in #5 then I explain what is wrong

Can't use IF within IFS
IFS Syntax must end with True Value in end
Example -
Excel Formula: 
=IFS(A2>89,"A",A2>79,"B",A2>69,"C",A2>59,"D",TRUE,"F")

At some places you used SUMIFS and somewhere SUMIF both have different syntax

At one place you actually didn't close the SUMIFS function after that all got messy.

At some places you were using Absolute references at other places you missed

Last, but not least, for some References you missed Sheet name

I tried correcting them all - Try the formula I gave in #5 and see the above differences.

Best wishes
 
Upvote 0
Peter_SSs said:
Welcome to the MrExcel board!

Could you make up a small set of dummy data for each of the worksheets, post those with XL2BB and explain your expected results in relation to that sample data?
Click to expand...
Peter_SSs said:
Welcome to the MrExcel board!

Could you make up a small set of dummy data for each of the worksheets, post those with XL2BB and explain your expected results in relation to that sample d
Click to expand...
Hi Peter!

I think I posted correctly below with XL2BB but I am not sure, just in case I added screenshots as well. The formula in cell C6 as it stands is only pulling in a number if all of the filters in cells C1:C4 are selected on anything besides total. When total is selected the sumifs does not work as there is not total in the data set. Basically what I was looking to do was remove the part of the sumifs when total is selected. Meaning if BTID is filtered on total then the "data!F:F,summary!C1" would be taken out as there would be no criteria or parameter required for BTID. Let me know if this makes sense! I appreciate the help!

Sample Data File.xlsx
C
6499.0833
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)


Data:
1659491513921.png


Formula: SUMIFS(data!H:H,data!D:D,summary!C4,data!E:E,summary!C3,data!F:F,summary!C1,data!A:A,summary!C2)
1659491536459.png
 
Upvote 0
kkspm13 said:
I think I posted correctly below with XL2BB but I am not sure,
Click to expand...
You need to select the range that you want to display before clicking Mini-Sheet. Could you try again?
 
Upvote 0
SanjayGulatiMusafir said:
I think I nailed down what you want by splitting it in lines. Try using this formula underneath

Excel 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!H4,Data!$C:$C,Rank!H5),

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),

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"),
SUMIFS(Data!G:G,Data!$A:$A,Rank!$B8,Data!$C:$C,Rank!$H$5),True,"")
Click to expand...

kkspm13 did you try the above formula in post #5?​

 
Upvote 0
You must log in or register to reply here.

Similar threads

H
  • Question
SUMIFS
Replies
2
Views
299
Haree
H
D
  • Question
Need help finding formula to pull then rank rates based on separate column
Replies
2
Views
206
felixstraube
F
R
  • Question
Applying a formula to a subset of rows
Replies
3
Views
145
rodqntr
R
gtd526
  • Solved
Move selected row to top of range
Replies
6
Views
128
gtd526
gtd526
T
  • Solved
RANK with INDEX MATCH
Replies
4
Views
621
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,940
Messages
6,122,352
Members
449,080
Latest member
Armadillos

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