Sum Records That Match a Criterion

March 29, 2022 - by Bill Jelen

Problem: That COUNTIF function is cool. Is there a way to sum all records that match a criterion?

Strategy: There is a SUMIF function that works similar to COUNTIF. In this case, you would look at all values in E8:E63 to see if they are equal to “Accounting”. If they are, you want to add up the corresponding value from F8:F63.

Instead of counting people by department, you want to add the total salary per department. =SUMIF($E$8:$E$63,A2,$F$8:$F$63). — Figure 405. Sum values from F if E is the right department.

The one difference from COUNTIF is that the SUMIF function usually requires you to specify the sum range as the third argument. (I say usually, because you might sometimes want to add up all salaries over $60000. In that case, the first and third arguments would both be F8:F63, so you can omit the third argument).

Additional Details: Starting in Excel 2007, Microsoft added an AVERAGEIF function. This seems fairly redundant to me, since you could easily do =C2/B2 in the current example rather than doing an AVERAGEIF formula.

This article is an excerpt from Power Excel With MrExcel

Title photo by Martin Sanchez on Unsplash