Sumifs Help

[kkspm13]

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:

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?

 

[SanjayGMusafir]

I am trying to get it to work now.. it is still pulling in a blank

Let me check it again, You also check if cell references are correct. Also in the formula you used "Total" everywhere so if the values in reference cell is not "Total" it will give blank as result.

 

[SanjayGMusafir]

I am trying to get it to work now.. it is still pulling in a blank

In row 5 & 6 cell references are not absolute. May be that too causing some problem.

from

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

change to

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

and try again

 

[kkspm13]

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

It Does not work with some filters

But others it does:

I am wondering if the formula may need to be arranged in the order of the filters?

 

[SanjayGMusafir]

It Does not work with some filters
View attachment 70679

But others it does:
View attachment 70680
I am wondering if the formula may need to be arranged in the order of the filters?

Still trying to understand where things are going wrong.

Can you post the formula you are actually using to let me check where things went wrong.

 

[kkspm13]

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

 

[Peter_SSs]

Can we get the sample data with XL2BB?

Also, what is the name of the sheet that the formula is on?

 

[SanjayGMusafir]

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

See you are using "Total" everywhere in the formula. where as it should changed according to what you want the condition to be for a particular SUMIFS

Change them as per your intended need and then check the formula result(s)

 

[kkspm13]

Thank you!! This works now!! I really really really appreciate all of your help!! Thank you!

 

[SanjayGMusafir]

Thank you!! This works now!! I really really really appreciate all of your help!! Thank you!

Please mark the thread as solved and also the mark the post that solves your problem.

Thanks & Best wishes

 

[Peter_SSs]

This works now!!

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.