Excel Countif – Count Number of Cells With Specific Data

When doing statistical analysis in Excel, you’ll often need to count the entries that met a specific criteria; e.g. how much a text, number, or alphabetical entry occurs. To do that, you’ll need to use one the COUNTIF function.

The COUNTIF function in Excel allows to count the number of occurrences of a number, text, or alphabetical entry, within a range of cells. The syntax of the COUNTIF function is as follows: =COUNTIF(Range_of_cells, Criterion)

Note that the COUNTIF function is used when one criterion is used, and is then different from the other function COUNTIFS that is used if multiple criteria need to be included in determining which records to count. So, COUNTIF if one criterion, COUNTIFS if multiple criteria.

In this tutorial, we will see how to use the COUNTIF function to count the number of occurrences of a number, and of a text, based on a specific criteria; and we’ll see how to use COUNTIF along with a logical operator (Not Equal To), as well as how to use it to count occurrences of only a part of a value.

Count number of cells with specific text in Excel

To count the number of cells in Excel containing a specific text, use the COUNTIF function as follows:

1. In the result cell, type: =COUNTIF(
2. Select the cell range where to look for values
3. Type a comma to separate function arguments
4. Type a quotation mark ” to indicate a text
5. Type the criterion (the text to look for)
6. Type the end quotation mark “
7. Type the closing parenthesis )
8. Then, press Enter.

For a simple example of using COUNTIF in Excel to find a text occurrences that meet a criterion, I will use the below sample spreadsheet which lists almost 20,000 customers along with where they live.

I need to answer the question “How many customers are in Ohio?”; so I will use COUNTIF function to count the number of occurrences of “OH” in the State column. The COUNTIF function will have column D (State) as a range of cells and the text “OH” (for Ohio) as a criterion.

The formula in cell I2 should be as follows: =COUNTIF(D:D,”OH”)

In this spreadsheet, 14,169 customers live in Ohio.

Note: You can select the entire column (in m y case, column D) by clicking on its header (in my case, labeled D), or by using the keyboard shortcut Ctrl + Space.
To select an entire row: Click on its header (number label) or use the keyboard shortcut Shift + Space.

Count occurrences of a number in Excel

To count occurrences of a number in Excel, use the COUNTIF function as follows:

1. Click in the result cell.
2. Type the following: =COUNTIF(
3. Select the cell range where to look for values.
4. Type a comma to separate function arguments.
5. Type the criterion (the number to look for).
6. Type the closing parenthesis ).
7. And press Enter.

To show the use of COUNTIF in Excel to find a number occurrences, I will use the below dataset, especially column E (Zip).

I need to answer the question “How many customers are in zip code 20901?”; so I will use COUNTIF function to count the number of occurrences of 20901 in the Zip column. The COUNTIF function will have column E (Zip) as a range of cells and the number 20901 as a criterion.

The formula in cell I3 should be as follows: =COUNTIF(E:E,20901)

In this spreadsheet, 35 customers live in zip code 20901.

Note: You can select the entire column (in m y case, column E) by clicking on its header (in my case, labeled E), or by using the keyboard shortcut Ctrl + Space.
To select an entire row: Click on its header (number label) or use the keyboard shortcut Shift + Space.

Count cells that don’t contain a specific text in Excel

To count the number of cells in Excel that don’t contain a specific text, use the COUNTIF function along with the logical operator Not Equal To:

1. In the result cell, type: =COUNTIF(
2. Select the cell range where to look for values.
3. Type a comma to separate function arguments.
4. Type a quotation mark ” to indicate a text.
5. Type <> (less than and greater than symbols).
6. Type the criterion (the text to look for).
7. Type the end quotation mark “.
8. Type the closing parenthesis ). Press Enter.

As an example, I will answer the question “How many customers are NOT on “Open Plan”?” from column B of the following spreadsheet.

I will use the COUNTIF function to count the number of occurrences of the text “Open Plan” in the “Product description” column, but with the use of the logical operator “Not Equal To” or <>. Here, I’ll need to select the specific range of cells from column B (i.e. B2:B18578) , and not the whole column; otherwise, Excel will give me an inaccurate result as it will also count the blank cells as not equal to “Open Plan”. You can easily select the specific range filled with data, no matter how much it is long, by clicking on the top cell, then using the keyboard shortcut Ctrl + Shift + Down Arrow.

The formula in cell I4 should be as follows: =COUNTIF(B2:B18578,”<>Open Plan”)

In this spreadsheet, 9,837 customers are not on the plan “Open Plan”.

Note: I easily selected the range B2:B18578 by clicking the top cell (B2), then using the keyboard shortcut Ctrl + Shift + Down Arrow.

By the way, the list of the logical operators is contained in the following table:

Logical operators labels	Logical operators symbols
Equal to	=
Not equal to	<>
Greater than	>
Less than	<
Greater than or equal to	>=
Less than or equal to	<=

Count occurrences in Excel of numbers greater than a value

To count the number of cells in Excel that contain a value greater than a specific number (or greater than or equal to), use the COUNTIF function and the logical operator Greater Than (or Greater Than or Equal To depending on your case):

1. In the result cell, type: =COUNTIF(
2. Select the cell range where to look for values.
3. Type a comma to separate function arguments.
4. Type a quotation mark “.
5. Type “greater than” symbol >.
6. Type the criterion (the number to look for).
7. Type the end quotation mark “.
8. Type the closing parenthesis ). Press Enter.

As an example, I will answer the question “How many customers are equal to or above customer number 194000000000000?” from column A of the following spreadsheet.

I will use the COUNTIF function to count occurrences of the number 194000000000000 in the “Account number” column, along with the logical operator “Greater than or equal to” or >=.

The formula in cell I5 should be as follows: =COUNTIF(A:A,”>=194000000000000″)

In this spreadsheet, 1,268 customers have an account number equal to or above 194000000000000.

Note: You can select the entire column (in m y case, column A) by clicking on its header (in my case, labeled A), or by using the keyboard shortcut Ctrl + Space.
To select an entire row: Click on its header (number label) or use the keyboard shortcut Shift + Space.

The logical operators you can use are listed in the following table:

Logical operators labels	Logical operators symbols
Equal to	=
Not equal to	<>
Greater than	>
Less than	<
Greater than or equal to	>=
Less than or equal to	<=

Count occurrences of a partial value in Excel using COUNTIF

The COUNTIF function can also be used to find only a part of the content of a cell and return the number of times it occurs.

To count the number of cells in Excel that contain a part of a specific value, use the COUNTIF function and the wildcard character * as follows:

1. In the result cell, type: =COUNTIF(
2. Select the cell range where to look for values.
3. Type a comma and a quotation mark “.
4. Type a wildcard character *
5. Type the criterion (the text to look for).
6. Type the end wildcard character *.
7. Type the end quotation mark “.
8. Type the closing parenthesis ). Press Enter.

Let’s practice with the following example. Earlier I showed you an example where I counted the number of customers who are not on exactly “Open Plan”. Taking a closer look at this spreadsheet, there are a number of plans which contain the word “open” other than the plan called “Open Plan”.

A better question to ask may be “How many customers are NOT in an Open type?”.

In my case, I want to count the number of cells that DON’T contain our partial text, so I use the Not Equal To logical operator before the first wildcard character.

My formula in cell I6 should be as follows: =COUNTIF(B2:B18578,”<>*open*”)

In this spreadsheet, only 65 customers are not on some type of open plan.

Note: I easily selected the range B2:B18578 by clicking the top cell (B2), then using the keyboard shortcut Ctrl + Shift + Down Arrow.

Wrap Up

So, that was the tutorial about COUNTIF, the Excel function that allows to count the number of occurrences of a text, a number, alphanumeric data, or a part of them, or if you want the function that counts the number of cells in Excel based on one criteria.

We saw how to use COUNTIF function to count occurrences of a number, occurrences of a text, how to use it with a conjunction of a logical operator, as well as how to count occurrences of only a part of a value.