MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2020: Handle Plural Conditions with SUMIFS

September 03, 2020 - by Bill Jelen

Excel Handle Plural Conditions with SUMIFS. Photo Credit: Nina Maalej at

Did you notice the “S” that got added to the end of SUMIF starting in Excel 2007? While SUMIF and SUMIFS sound the same, the new SUMIFS can run circles around its elder sibling.

The old SUMIF and COUNTIF have been around since Excel 97. In the figure below, the formula tells Excel to look through the names in B2:B22. If a name is equal to the name in F4, then sum the corresponding cell from the range starting in D2:D22. (While the third argument could be the first cell of the sum range D2, it will make the function volatile, causing the worksheet to calculate more slowly.)

A summary table built with SUMIF. Go find all of the sales made by this person.

SUMIF and COUNTIF were great with only one condition. But with two or more things to check, you had to switch over to SUMPRODUCT, as shown below. (I realize most people would replace my multiplication signs with commas and add a double-minus before the first two terms, but my version works, too.)

Before SUMIFS was introduced, you would have to use SUMPRODUCT when you needed to check for two conditions.

SUMIFS allows for up to 127 conditions. Because you might have an indeterminate number of conditions in the function, the numbers that you are adding up move from the third argument to the first argument. In the following formula, you are summing D2:D22, but only the rows where column B is Allen Matz and column C is Widget. The logic of "Sum this if these conditions are true" is logical in SUMIFS.

The formula =SUMIFFS($D$2:$D$22,$B$2:$B$22,$F4,$C$2:$C$22,G$3) says to sum the quantities in D when the sales rep in B matches F4 and when the Product in C matches G3.

Excel 2007 also added plural versions of COUNTIFS and AVERAGEIFS. All these “S” functions are very efficient and fast.

Thanks to Nathi Njoko, Abshir Osman, Scott Russell, and Ryan Sitoy.

Title Photo: Nina Maalej at

This article is an excerpt from MrExcel 2020 - Seeing Excel Clearly.

Bill Jelen is the author / co-author of
Excel Dynamic Arrays Straight to the Point 2nd Edition

Fifteen months after Dynamic Arrays debuted for Office Insiders, the functions are being released to General Availability. This second edition of the book has been updated with new examples: see how Dynamic Arrays make XLOOKUP better. The chapter on the logic behind arrays has been expanded.