SUMIFS Excel SUM with filters galore


Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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.

sumifs excel sum with filters galore microsoft excel 30630 - SUMIFS Excel SUM with filters galore

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.

subs profile e1563205311409 - SUMIFS Excel SUM with filters galore
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address