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

Join Office for Mere Mortals today

Office for Mere Mortals is where thousands pick up useful tips and tricks for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  We've never spammed or sold addresses since we started over twenty years ago.