Syntax of COUNTIF Formula
Example of COUNTIF Formula
Possible Errors returned by the COUNTIF Formula

COUNTIF formula in Excel counts the numbers of cells in a range that meet the given condition.

COUNTIF Formula Syntax

COUNTIF Formula has two parts:

COUNTIF (range, criteria)

range

range is the range in which the value that you are trying to find out is located.

criteria

criteria is the value whose occurence in the above range we would like to count. The criteria can be a string (Ex “Bill”), a number (Ex. 15) or a combined expression (“>=”&15).

Example of a COUNTIF Formula

Using COUNTIF to find the number of times a string appears in a range

Let’s look at an example of the COUNTIF formula. Suppose we wanted to find out the number of time a particular value (“Bill Wanton”) appears in the range as shown in the example above. We could simply write =COUNTIF(A2:A10,F2). The result in this case will be 4 as shown in the example above.

Using COUNTIF to find the number of times a string appears in a range using the wildcard character

Now if we wanted to find the number of times a name starting with “T” appears in the range. The formula =COUNTIF(A2:A10,”T*”) would have done the trick here.

Using COUNTIF as an Array Formula

Now suppose we wanted to know the number of times each name appears in the range. The simple way to do is to simply write =COUNTIF($A$2:$A$10,A2) in an adjacent column. However if we wanted to, we would have used the array formula and entered it as =COUNTIF(A2:A10,A2:A10) with a CTRL+SHIFT+ENTER. (See Array Formulas for greater details)

Using COUNTIF to find the number of times a numeric value appears in a range

We have seen the example of the above where we try to find out the occurrence of a single string. The formula pretty much stays the same if you want to count the number of times a numeric value appears in the range. Say you wanted to find the number of times sales have been less than 100 units. You could find that out using =COUNTIF(D2:D10,”< =100″).

Using COUNTIF with a combined expression

Using COUNTIF to find the number of times a numeric value appears using a combined expression – is where COUNTIF gets interesting. Suppose you wanted to find the number of times a manger does not appear in the list. You could write =COUNTIF(A2:A10,”<>”&F2) where the cell F2 contains the name of the manager. This way, whenever the value of cell F2 changes, the formula returns the new value.

How to enter the COUNTIF formula in an Excel Sheet

1. Select the cell in which you want to place the formula

2. Type the formula as =COUNTIF(

3. Then using the keyboard up-down and left-right arrow key, move the cursor to the first (top-left) cell of the range in which we would like to count the occurrence of a string.

4. Keeping the SHIFT button pressed, move the cursor to the last cell (bottom-right) of the range.

4. Press the comma key (,)

5. Then either enter the value you would like to know the frequency of appearance of – if its a string enclose in quotes (Ex. “Bill’), if its a number enter as such, if its an expression enter it within quotes (Ex. “< =100”) along with the “&” operator (Ex “<=”&F2) (F2 is the cell which contains the value and we are trying to find out all occurrences which are less than this value)

6. Close the formula bracket by typing in the closing bracket ).

Check out the clip above for knowing if the values you’ve entered are in the same order. In the end your formula should look something like this =COUNTIF(D2:D10,”< =100″)

Possible Errors with the COUNTIF Formula

The COUNTIF formula can result in the following error values:

COUNTIF #NAME? Error

The #NAME? Error value in COUNTIF formula can occur when you incorrectly specify the range in first parameter. Suppose you mistyped A2 as AAA2 in the formula (Ex. =COUNTIF(AAA2:A10,F2) ) you will get a #NAME? error.

You can download an example of COUNTIF formula here or click on the button below: