Excel 2024: Handle Plural Conditions with SUMIFS

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

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

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.

Excel 2007 also added plural versions of COUNTIFS and AVERAGEIFS. In February 2017, Microsoft 365 added MAXIFS and MINIFS. All these "S" functions are very efficient and fast.

Caution: The first argument of these functions must be a range. It can not be a calculation. If you need a calculation for the first argument, enter that calculation in the grid.

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