SUMIFS with variable number of filters

[NPatrick617]

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 sample file, 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?

[#2]

@NPatrick617 

=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?

[#3]

@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. :)

[#4]

@NPatrick617 

=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.