Excel: Sum Records That Match a Criterion

This page is an advertiser-supported excerpt of the book, Power Excel 2010-2013 from MrExcel - 567 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.


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.

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