Tips for Excel, Word, PowerPoint and Other Applications

Basic SUMIF and COUNTIF

Why It Matters To You

SUMIF and COUNTIF are nice simple functions that let you add a conditional qualifier to a SUM or COUNT function, basically, only SUM or COUNT IF the specified conditions are met. Unfortunately, the allowable conditions are pretty limited. You are pretty much limited to a single conditions, meaning that you can't SUM only the numbers between 2 and 9; you can't sum the sales figures that are both Western Region AND for a specific salesman ... at least not with the basic SUMIF and COUNTIF.

How To ...

The functions are pretty straight foward. For COUNTIF, you need 2 elements: the range of cells to be evaluated (Range) and the matching criteria (Criteria). Logically, Excel starts at the top of your range and tests each one to see if it equals the matching criteria. If it does, it adds it to the count. If it doesn't, then it disregards it ... so on and so forth, until it reaches the end of the range, at which point it returns the value.

SUMIF is almost like COUNTIF, but adds a third element, the range of cells to sum (Sum_range), when the match criteria is met. Logically, the function acts like COUNTIF, but when the match criteria is met, it adds the corresponding cell from the sum range to the count.

OK, that was easy enough. A couple of quick notes. First, your ranges in a SUMIF must be the same size or you'll get an error value. Second, the utility of a COUNTIF or SUMIF expands considerably if you use a variable as the criteria and are smart about using relative and absolute references. So, if I'm summing total sales by salesman, and I have a list of salesmen, link to the name as your criteria. This gives you a single formula that you can drag to sum sales for the entire salesforce. Lastly, the utility of this can be further improved if you used a pivot table to generate a unique list of salesmen from the production report.

Here's an example of what I mean. I used the simple date set sumif.xls to generate a simple summary using SUMIF and COUNTIF. I'm summarizing the total amount by name as well as counting the number of entries. By using variables with a mixed reference for the criteria and making both the range and sum_range absolute references, I have a single formula that can be copied down the entire table.

The only thing that changes when I count the number of entries is a slight change to the function name and the elimination of the sum_range argument.

Notes

Last updated	8/15/08
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	None
Related Tips	Advanced COUNTIF/SUMIF Advanced SUMPRODUCT Pivot Tables Introduction to Arrays (aka What are those {} things?)