Sumifs Help

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?
 
You're welcome .. (but we could have got there much earlier - ref posts 3, 8, 16 ;))

It seems that you were getting the #NAME? error due to not having the LET function. That function became available to 365 subscribers quite some time ago so, as I hinted at before, I suspect that you could have some updates to do. If at some point you apply the updates and get the LET function then you can use the even shorter formula from post 25.
 
Upvote 0

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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