- Excel Book Excerpt
Excel Sum Records That Match a Criterion
This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 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.
Figure 412 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.
For more resources for Microsoft Excel: