Toggle between sumif and sumifs

0
votes

I have data from different quarters (Q1 to Q4) and a sumifs formula that takes the selected quarter as a parameter. What if I want to sum data from the entire year (all 4 quarters)?

I can do an if statement nesting a sumifs if a quarter is selected or a sumif if Yr2021 is selected but is there a more elegant formula to toggle between the two?

Edit: thousands of rows, quarter in column M. Drop down with 5 options, 4 quarters and 2021. Current formula is a sumifs accounting for quarter. When user selects year option, formula returns 0 because column M is labeled Q1 to Q4.

1
excelexcel-formula
You could use SUM with SUMIFS. - BigBen
My fault, I wasn't clear. My data is only labeled Q1 to Q4, no year. Edit: oh, sum 4 sumifs? How does that look like? - findwindow
No, wrap the SUMIFS in SUM and use brackets around your (multiple) criteria. Well, a screenshot of your setup would be really helpful here. - BigBen
No like =SuMPRODUCT(SUMIFS(Area,rng,{"Q1","Q2",...})) - Scott Craner
I think nesting the IF inside the SUMIFS() might be a better option also, but hard to know without more information. - Scott Craner

1 Answers

2
votes

Use an IF inside the SUMIFS like this:

=SUMIFS(A:A,M:M,IF(Z1="yr2021","*",A1))

Z1 would be the location of the drop down. if it equals "yr2021" it return all results otherwise it filters on the drop down.

Change A:A to your number data column to be summed.