COUNTIF for Excel


The COUNTIF worksheet function in Microsoft Excel has a simple format that provides a quick and easy way to count the number of times a certain condition is met in a data set.

By Kara Monroe

The COUNTIF worksheet function in Microsoft Excel has a simple format that provides a quick and easy way to count the number of times a certain condition is met in a data set.

That sounds imposing but it isn’t. Most likely you already use COUNT to count the number of cells that have numbers or COUNTA to count the non-blank cells in a worksheet. COUNTIF simply adds the ability to put additional conditions on what is counted in the cell range, which is easier and faster than trying to create a filter on results then count them.

While COUNTIF is quite simple, it can be used to create more complex statements that provide you a great deal of control over what is returned in your dataset.


COUNTIF BASICS

The basic syntax of the COUNTIF function is =COUNTIF(range, criteria). Your criteria, if text or conditional – both examples we’ll look at a bit later – must be in double quotes. The COUNTIF worksheet function is easy to understand from an example so let’s take a look at a simple example using numerical entries.

       A

1     5

2     7.5

3     5

4     5

5     7.5

To find out the number of times that 5 is used in this range of data, you would use the formula =COUNTIF(A1:A5,5). The formula will return a value of 3. Likewise, using =COUNTIF(A1:A5,7.5) you will get a value of 2. There’s no need to put an equals sign in to make a valid condition.

$$PAGE$$

COUNTIF CRITERIA

The “criteria” part of the COUNTIF syntax allows you to count items that meet a condition such as greater than (>), greater than or equal to (>=), less than (<), less than or equal to (<=), or not equal to (<>). Using the example above, the formula =COUNTIF(A1:A5,”>5″) will return a value of 2. Notice that the criteria in this case is in double quotes.

Tip: Double quotes must be used for any criteria other than a single number.

The COUNTIF function can also be used to count text entries when text entries are enclosed in quotes. Let’s consider another example.

       A

1     cat

2     cow

3     dog

4     cat

5     cat

To total the number of instances that cat appears in the range from A1 to A5, use the formula =COUNTIF(A1:A5,”cat”). This formula returns a value of 3.

$$PAGE$$

WORD VALUES AND COUNTIF

The COUNTIF function does not need to be used to count whole words. Let’s say for instance that you have a long listing of names and you want to find out how many names start with something from B on down to Z. In this case, you will use a comparison operator like those described earlier for numbers. Again, an example makes this concept easiest to see. Let’s take a look at this example:

       A

1     a

2     ab

3     abc

4     b

5     bc

6     bcd

7     c

8     cd

9     cde

10   e

Using the formula =COUNTIF(A1:A10,”>b”) would return a value of 6 since lines 5 through 10 all contain values that are ‘greater than’ the letter b. The comparison selection does not need to be an item in your list. For instance, you could use the formula =COUNTIF(A1:A10,”

Comparing words or other combinations of letters is simply an alphabetical comparison. For example if comparing the words cat and cow, cow is greater than cat because cow would come after cat alphabetically. In this same way, the word cats is greater than cat since it has an additional letter. It’s also important to note that upper and lowercase letters are treated exactly the same so the COUNTIF function will view the words cat, CAT and Cat as being exactly the same.

Criteria can also be made up of other functions such as the TODAY() function which returns the current date. Functions must be concatenated, or added to, the comparison operator using the ampersand (&) character. For example, if you have a list of dates and want to find out how many come after the current date, you would use =COUNTIF(A1:A10,”>” & TODAY())

Tip: Functions have to be outside the quotes. Use the & sign to join them into a condition.

$$PAGE$$

USING WILDCARDS WITH THE COUNTIF FUNCTION

The COUNTIF function can be used with two different wildcard characters – the asterisk (*) and the question mark (?). The asterisk is used to search for anything regardless of the number of characters in the response. For example, if you wanted to search the list shown below for any cell containing a letter b, you would use the function = COUNTIF(A1:A10,”*b*”). The use of asterisk both before and after the letter b tells Excel to search for the letter b and to ignore what is both in front of it and behind it. You should get a result of 5 from this formula, since 5 cells contain the letter b. You might also consider searching for only those entries that begin with the letters ab using the formula =COUNTIF(A1:A10,”ab*”). This returns a result of 2 since both the values in cell A2 and A3 begin with ab.

       A

1     a

2     ab

3     abc

4     b

5     bc

6     bcd

7     c

8     cd

9     cde

10   e

The question mark (?) provides a much more specific search. The ? substitutes for one character. A search criteria can include multiple question marks to indicate multiple missing letters. For example, given the list of names below, you could use the formula

=COUNTIF(A1:A4,”wil???”) to return the value of 2 for both Willis and Wilson (each has six characters and begin with wil. This contrasts with the use of the asterisks after the criteria “wil” which would give a result of 4 since all 4 values begin with the letters wil. The question mark provides a great deal of control when using the COUNTIF function.

        A

1     Wills

2     Willis

3     Wilson

4     Wilsons

$$PAGE$$

USING COUNTIF STATEMENTS IN WITH MATHEMATICAL OPERATIONS

You can use a COUNTIF statement as part of a mathematical expression. Consider the following set of numbers:

       A

1     1

2     1

3     2

4     3

5     5

6     8

7     13

8     21

If you use the formula:

=COUNTIF(A1:A8,”>2″)+COUNTIF(A1:A8,”<13"), you are adding together the number of values greater than 2 (5) with the number of values less than 13 (6) which will return a total result of 11. Again, any type of criteria - words, letters, numbers, dates, etc. - can be used in these statements.

$$PAGE$$

VOTE COUNTING

One example in the Excel help is a good use of COUNTIF. You can use it to calculate the percentage results in a simple poll. Assuming the results are in an Excel worksheet in a range of B2:B99 then the formula to count the ‘Aye’ votes is COUNTIF(B2:B99, “Aye”) and the ‘Nay’ votes COUNTIF(B2:B99, “Nay”).

To turn those raw numbers into a percentage just divide by the total number of votes and make the cell formatting a percentage. COUNTIF(B2:B99, “Aye”) / COUNTA(B2:B99).

Microsoft uses a more convoluted option to count the non-blank cells ROWS(B2:B99) – COUNTIF(“<>“&”*”) which only shows there are more than one way to skin an Excel cat.

$$PAGE$$

WHEN TRUE ISN’T TRUE

The strange thing about COUNTIF is that it doesn’t seem to obey the normal logic rules that would allow integration of a wider range of functions.

For example COUNTIF(A1:A8, “TRUE”) should return 8 since the condition is always met but you get 0 instead. COUNTIF(A1:A8, TRUE) doesn’t work either.

That’s the way it is but a pity because COUNTIF could have had greater potential.

 


Want More?

Office Watch has the latest news and tips about Microsoft Office. Independent since 1996. Delivered once a week.