Using Excel’s COUNTIF FunctionLearn what the COUNTIF function is, what it does, and what you can do with it
Excel has a ton of features and functions that can make working with data much easier.
Among these functions is COUNTIF.
What this function does is count the number of cells that meet the conditions set by the user.
For example, you can use the COUNTIF function to count the number of cells within a specified range (of cells) that contain a numerical value that is greater than 100.
You can also use it to count the number of cells within a specified range (of cells) that contain a specific text string (e.g. a name, profession, etc.).
It’s a very versatile function that has tons of applications.
In this article, we will be exploring what the COUNTIF function is.
We’ll be figuring out what it does, and what we can do with it.
By the end of the article, you should have a deeper understanding of the COUNTIF function.
Let’s get started.
The COUNTIF Function
The COUNTIF function is designed to count the number of cells that meet the conditions set by the user.
The user must define the range and criterion or condition. You will see this in the formula for using COUNTIF, which is as follows:
=COUNTIF(range,criteria)
Where
range – specifies the range of cells from which the COUNTIF function will count. For example, if this was set to A1:A11, the COUNTIF function will count the number of cells within that range that meet the criterion
criteria – defines the criterion or condition that prompts the COUNTIF function which cells to count. The criteria can be a text string, number, expression, or cell reference. For example, if it’s set to “apples”, the COUNTIF function will count the cells within the specified range that contains the value “apples”.
COUNTIF Example
For example, let’s say we have this dataset:
This is a list of players for a game (it goes up to row 301).
It contains Player ID data, the server they play in, and the class of their character.
We’ll be using the COUNTIF function to count the number of players on the Ceole server.
The formula will then be =COUNTIF(B:B,”Ceole”). The range is set to B:B because column B contains the server values.
This is where the COUNTIF function will perform its count. criteria is set to “Ceole” because we want to count the number of cells that contain the Ceole text string. (Note: you need to enclose criteria in quotation marks if it’s not a number or cell reference)
The COUNTIF function counted 79 cells that meet the conditions we set.
This means that there are currently 79 players playing on the Ceole server. (Note: the criterion is not case sensitive; this means that even if we set it to “ceole”, the COUNTIF function will still count 79 cells)
Use COUNTIF to Count Blank Cells
For the first application of COUNTIF, we’ll be using it to count blank cells within a specific range.
The formula that we’ll use is as follows:
=COUNTIF(range,””)
Here, the criterion is set to “” which prompts the COUNTIF function to count blank cells. Here’s a visual example:
Here, the total cell count of the range is 72. The COUNTIF function counted 48 blank cells using the formula =COUNTIF(A1:C24,””).
Note that Excel has a specific function for counting blank cells, which is COUNTBLANK. Using it will produce the same result as above.
Use COUNTIF to Count Non-Blank Cells
Since we already know the formula for counting blank cells, the next COUNTIF formula we’ll learn will count non-blank cells. Here’s the formula:
=COUNTIF(range,”<>”)
This formula will count the cells that contain any values such as text, number, date, etc. It also has another variation:
=COUNTIF(range,”<>”&””)
Here’s a visual example of how the formula (and its variation) works:
The total cell count of the range is 72. The COUNTIF function counted 24 non-blank cells using the formula =COUNTIF(A1:C24,”<>”).
Use COUNTIF to Count Cells with Text
If you specifically want to count cells that only contain text (any text), then the formula you should use is as follows:
=COUNTIF(range,”*”)
This formula uses the wildcard * which acts as a substitute for any text values (including empty strings such as three spaces).
It will not count cells that contain only numbers and/or dates.
Here’s a visual example of how the formula works:
The total cell count is 72. There are 51 non-blank cells. The COUNTIF function counted 27 cells with text using the formula =COUNTIF(A1:C24,”*”).
Use COUNTIF to Count Cells without Text
If you want to count cells without text, then the formula you’ll be using should be this:
=COUNTIF(range, “<>”&”*”)
This will count cells, including blank cells, that do not contain any text. Here’s a visual example:
The total cell count is 72. The COUNTIF function counted 45 cells that does contain text using the formula =COUNTIF(A1:C24, “<>”&”*”).
Use Wildcard Characters with COUNTIF
The next set of formulas will include the use of wildcard characters (e.g. *,?)
Use COUNTIF to Count Cells That Contain a Word, Letter, or Text String
If you want to count the number of cells that contains a particular letter, word, or text string in it, the formula you’ll be using is:
=COUNTIF(range, “*criteria*”)
For example, we have this dataset:
From this dataset, we want to count the cells that contain the word “cookie”.
As such, we’ll be using the formula =COUNTIF(B2:B16,”*cookie*”).
This will count all the cells within the range that has the text string cookie in it.
The COUNTIF function counted 4 cells within the range that contain the text string cookie.
Use COUNTIF to Count Cells That Start With a Word, Letter, or Text String
If you want to count the cells that start with a specific letter, word, or text string, the formula you’ll be using is as follows:
=COUNTIF(range, “criteria*”)
For example, let’s say we have this dataset:
We want to count the cells that start with the word “Green”.
As such, we will use the formula =COUNTIF(A2:A13,”Green*”).
The COUNTIF function counted 3 cells within the range that start with the word Green.
Use COUNTIF to Count Cells That End With a Word, Letter, or Text String
What if you want to count the number of cells that end with a particular letter, word, or text string?
Then you’ll be using this formula:
=COUNTIF(range, “*criteria“)
Let’s use the same dataset above.
This time, we’ll be counting the cells that end with the word “Cat”.
The formula we’ll be using is =COUNTIF(A2:A13,”*Cat”).
The COUNTIF function counted 4 cells within the range that end with the word Cat.
Use COUNTIF to Count Cells That Contain A Specific Number of Characters
Suppose you want to count the cells that contain a string that has exactly a specific number of characters (spaces included).
The formula you’ll be using is as follows:
=COUNTIF(range,num_char)
But in place of num_char, you’ll be using the special character “?”.
For example, you want to count cells that contain strings that only have 3 characters.
You should set num_char to “???”. The number of ? represents the number of characters.
Here’s a visual example:
With the formula =COUNTIF(A2:A13,”????”), the COUNTIF function counted the number of cells that contain strings that exactly have 4 characters.
Numbers and COUNTIF
In this section, I’ll be showing you formulas that work on numbers.
Count Cells That Contain a Numerical Value Equal to a Specific Number
If you want to count the number of cells within a range that contain a numerical value that is equal to a specific number, the formula you’ll be using should be:
=COUNTIF(range, “=number“)
For example, if you want to count the number of cells within the range A1:A21 that contain a value of 8, the formula you should use is =COUNTIF(range, “=8”).
Count Cells That Contain a Numerical Value Greater than a Specific Number
If you want to count the number of cells within a range that contain a numerical value that is greater than a specific number, the formula you’ll be using should be:
=COUNTIF(range, “>number“)
Count Cells That Contain a Numerical Value Less than a Specific Number
If you want to count the number of cells within a range that contain a numerical value that is less than a specific number, the formula you’ll be using should be:
=COUNTIF(range, “<number“)
Count Cells That Contain a Numerical Value Greater than or Equal to a Specific Number
If you want to count the number of cells within a range that contain a numerical value that is greater than a specific number, the formula you’ll be using should be:
=COUNTIF(range, “>=number“)
Count Cells That Contain a Numerical Value Less than or Equal to a Specific Number
If you want to count the number of cells within a range that contain a numerical value that is less than a specific number, the formula you’ll be using should be:
=COUNTIF(range, “<=number“)
You can also use a cell reference instead of a number for the above formula. Just make sure that the referenced cell contains a numerical value.
Use COUNTIF to Count Cells With Multiple OR Criteria
Suppose you want to count cells with a range that contains may meet at least one of two or more criteria.
What you need to do here is to add two or more COUNTIF functions.
Here’s a look on how the formula will look like:
=COUNTIF(range, “criteria1“)+COUNTIF(range, “criteria2“)+…
For example, I want to count the number of cells in this dataset that contain the words “cookie” or “bread”.
The formula I’ll use will be
=COUNTIF(B2:B16,”*cookie*”)+COUNTIF(B2:B16,”*bread*”).
The COUNTIF function counted 8 cells within the range that contain the word cookie or bread.
Conclusion
And that’s the COUNTIF function.
In this article, I was able to show different ways you can use the COUNTIF function.
Any other application I may have missed? Let me know in the comments.
I hope that you’ll be able to use your learning here in your future endeavors.