SUMIFS Excel SUM with filters galore

Office for Mere Mortals helps people around the world get more from Word, Excel, PowerPoint and Outlook. Delivered once a week. free.


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.


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.