MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Excel 2019: Handle Plural Conditions with SUMIFS

August 28, 2019 - 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

Bill Jelen is the author / co-author of
MrExcel 2020 - Seeing Excel Clearly

This is a 4th edition of MrExcel LX. Updates for 2020 include: Ask a question about your data, XLOOKUP, Power Query's Data Profiling tools, How Geography Data Types decide which Madison, A SEQUENCE example for descending 52 weeks, Exchange Rates support in Stock Data Types, How to collapse the Search box, How to leave effective feedback for Microsoft, How to post your worksheet to the MrExcel Board using XL2BB.