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