Tips for Excel, Word, PowerPoint and Other Applications

Advanced SUMIF and COUNTIF

Why It Matters To You

Sometimes your data just won't be clean enough where all entries are discrete and unique items. Sometimes, you may have several variants of names or item descriptions that you just know are the same. In these cases, you can use SUMIF and COUNTIF with wildcards (*) to expand the capabilities of SUMIF and COUNTIF. Again, the utility of these formulas will be somewhat dependant on how your dataset is organized.

How To ...

Consider the following data set (sumif.xls) showing sales by employee name. In this case, I have 3 names whom I know to be the same salesman: Don, Donaldson, and E, Donald. The reporting system just happens to report his name 3 different ways.

If I try a simple COUNTIF or SUMIF to calculate his sales, I would only be able to summarize sales by each name, not for all three. However, if I use a SUMIF or COUNTIF with wildcards, then I can hopefully summarize all three of his names in the same formula. The typical COUNTIF looks like this:

COUNTIF($B$5:$B$10,"don")

But it only returns the values for Don and not E, Don or Donaldson. However, if I use wildcards, before and/or after the name string, which might look like this:

COUNTIF($B$5:$B$10,"don*") COUNTIF($B$5:$B$10,"*don*")

Then I get results which include all three of the possible spellings. Here are the results using each of the 3 variations of wildcards with the core "don". There is a case with no wild cards (only returns Don), only a wild card after (picks up Don and Donaldson) and a wild card before and after, which returns all 3 values.

Here's what the COUNTIF formula with both wild cards looks like...

... and the corresponding SUMIF.

Here's an even more advanced application of COUNTIF and SUMIF. Let's say I want to identify all employees with a A or a B in their name. COUNTIF and SUMIF can help out. The test file has an example of this too.

This is just a simple application with a wildcard, but combined within an OR function.

Wildcards with numerical values seem to behave a little differently. In order for the wildcard to work, the number needs to be preceded by a ', which tells Excel to treat the value as text. Numercial values simply formatted as values or text won't work, as shown below.

You can also use COUNTIF and SUMIF with inequalities. However, in order for this to work, you need to encase your criteria in quotes, which is a slightly different way of thinking about things. If you recall from the Advanced SUMPRODUCT discussion, you DON'T need quotes when expressing inequalities there.

Likewise, you can use SUMIF and COUNTIF with dates. Just like the inequalities discussed above, you have to encase your date criteria in quotes.

Notes

Last updated	8/15/07
Application Version	Excel 2003
Author	Michael Kan
Pre-requisites	Basic COUNTIF/SUMIF
Related Tips	Advanced SUMPRODUCT Pivot Tables Basic Arrays