Forum Discussion
SUMIFS with variable number of filters
I am currently using Excel 2016.
I am trying to add up data using a SUMIFS statement - the user can select from several dropdowns to filter the data. However, in each dropdown, I would like to have the option of "Total", which essentially removes the filtering from that column. I have linked a https://docs.google.com/spreadsheets/d/1yvGZp4-7iinnkRGcAFUekhWT6OTRaH9l/edit?usp=sharing&ouid=101346927804985490917&rtpof=true&sd=true, where a user has two criteria to choose from. They can select one of the current criteria listed (A,B,D, etc) or a Total which will select every item.
I can use an IF combined with a SUMIF(S) when there is only one criterion, but once I have multiple different criteria listing, it gets out of hand.
Any thoughts on how to resolve this?
3 Replies
=IF(OR(L3="Total",L4="Total",AND(L3="Total",L4="Total")),SUM(E5:E10),SUMIFS(E5:E10,C5:C10,L3)+SUMIFS(E5:E10,D5:D10,L4))
Is this what you want to do?
OliverScheurich, thanks for your response. It does solve the problem...I was hoping there might be something that can scale (ie if I had a third column, how much bigger does the formula get).
If using an IF/OR/AND statement is the best way to do it, at least it's better than what I did. 🙂
=IF(COUNTIF(L1:L4,"Total")>=1,SUM(E5:E10),SUMPRODUCT(((A5:A10=L1)+(B5:B10=L2)+(C5:C10=L3)+(D5:D10=L4))*E5:E10))
This formula can easily be adapted to many more columns.
