Using SUMIFS to test multiple criteria

One of my all-time favourite Excel functions has been SUMPRODUCT – it’s versatility was endless, but was especially useful for when you wanted to SUM up some data with multiple conditions, such as sales of Pencils in the East region, perhaps.

Using SUMPRODUCT, we’d have written something like this… 

This works well, but as anyone who has worked with SUMPRODUCT a lot will tell you, you don’t need many of these formulas in your file before it starts grinding to a halt – and they take a little bit of practice to remember how to do them.

However, from Excel 2007, Microsoft introduced SUMIFS. Like SUMIF, it sums up data based on a condition. However with SUMIFS, you can add multiple conditions.

The syntax is pretty straightforward; F

First, tell it the range you want to ultimately add up, then provide your first range you want to test, followed by the condition. Then repeat the test range and condition for all your other conditions.

Or, using the Tables functionality we covered previously… (Now you start to see why Tables make so much sense?)

Similar to SUMIFS, you can also use COUNTIFS to do a similar task to counting…

(Note how you can use Greater Than & Less Than operators – but you must add them in quotes)

The great benefit of using SUMIFS over SUMPRODUCT is performance – SUMIFS will perform much, much faster than SUMPRODUCT, so for straightforward conditional summing, SUMIFS is the way to go.

And that’s really all there is to it. You can add pretty much as many conditions as you wish – up to 256, I believe.

Without a doubt, SUMPRODUCT still has its place, and will continue to be a fantastic function – I will re-work an article I had written in the past to show how to get the most from it!

2 comments