SUMIFS Excel SUM with filters galore
Consider switching to SumIFS, it’s much the same as the older Excel function, SumIf() but easier to expand as your needs change. It can add up values according to more than criteria like a name and date range.
We’ve talked about SUMIF and how its parameter order is a little strange. SumIF has filter/criteria first followed by the range to add up. That’s contrary to the usual practice of adding parameters to the end when a function is based on a previous version. SUM() has the range to be added first (obviously) so people rightly expect SumIF() to put the criteria after that.
SumIFS parameters
SumIFS() fixes that anomaly by putting the sum_range first, followed by pairs of criteria.
SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Sum_range – a range of cells to add up
Criteria_range1 The range to test with Criteria1.
Criteria1 – what to test against the criteria range. For example, criteria can be entered as 67, E2, “<67”, “apricots”, “01/01/2017” or “>01/01/2017”.
Add more filter pairs, SumIFS supports up to 127 criteria pairs. Though if you’re dealing with more than a few, it’s probably better to find a more manageable solution!
SumIFS replaces SumIF
Here’s an expanded version of our previous example. We’ve replaced the SumIF function with a simple SumIFS (ColF) then added to that to make other filtered additions.
The left table is called ‘Shorty’.
All – Col F uses =SUMIFS(Shorty[Value],Shorty[Name],E2)
This could be a SUMIF e.g. =SUMIF(Shorty[Name],E2, Shorty[Value]) but SumIFS lets us add on more criteria easily.
2019 – Col G add only values in 2019
=SUMIFS(Shorty[Value],Shorty[Name],E2,Shorty[Date], ">=01/01/2019", Shorty[Date], "<=31/12/2019")
2018 – Col H is for 2018 values only
=SUMIFS(Shorty[Value],Shorty[Name],E2,Shorty[Date], ">=01/01/2018", Shorty[Date], "<=31/12/2018")
To filter a date range takes two criteria to specify the start and end date.
Faster SumIFS coming to Excel 365 for Windows
SumIF, a better way to Sum in Excel
Three ways to make an auto-update unique list in Excel