Handle Plural Conditions with SUMIFS

January 20, 2020 Mr King Excel Tips,

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

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.

#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1