COUNTIF() Excel: Count Cells Based on Conditions

All the Different Ways To Use Excel COUNTIF() in Excel

Now that you know how the COUNTIF() function works in Excel, let's look at some more examples of where we can use it.

Counting cells that contain text

COUNTIF() can count how many times a specific text appears in data. For example, I have a list of fruits in A2:A10 and want to know how many times Apples appear. For this, I enter:

=COUNTIF(A2:A10, "Apple")

This formula will count all the cells in that range that exactly match Apple.

Count cells with text using COUNTIF(). Image by Author.

Counting numeric values

We can also count numbers greater than, less than, or equal to a specific value. For example, I have a dataset and now I can find different things from this with the COUNTIF() formula.  

• To count how many fruits have a quantity greater than 500: =COUNTIF(B2:B10,">500")

• To count how many fruits’ quantities are not equal to 500: =COUNTIF(B2:B10, "<>500")

• To find how many fruits are exactly 500 in quantity: =COUNTIF(B2:B10, “500”)

• To count how many fruits have quantity below 500: =COUNTIF(B2:B10,"<500")

Count numeric values using the COUNTIF() function. Image by Author.

Using wildcards for partial matches

Sometimes we want to count cells that contain a specific part of a word or phrase. In that case, we use wildcards. There are two types of wildcards:  

• * matches any number of characters.

• ? matches only a single character. 

For example, I have a fruits dataset and I use two different formulas here to show you how COUNTIF() handles wildcard cases. To count all the fruits that contain n anywhere in the fruit’s list, I enter the following formula:

=COUNTIF(A2:A10, "*n*")

This formula uses the asterisk wildcard character *, which represents any number of characters (including zero). By typing "*n*", Excel searches for cells where "n" appears anywhere in the text — at the beginning, middle, or end. The formula counts all matching cells in the range A2:A10 and returns the total count of fruits containing the letter "n".

To count all four-letter fruits that start with K, I type:

=COUNTIF(A2:A10,"K???")

This formula uses the wildcard character ?, which represents exactly one character. By typing "K???", Excel counts all cells in the range A2:A10 that start with the letter K and have exactly four letters and display the final result. 

Use wildcards in the COUNTIF() function. Image by Author.

Applying criteria from another cell

Instead of hardcoding the conditions, we can even use the cell reference. This makes the formula more flexible because it updates automatically when the cell value changes.

If I want to know how many fruits have a quantity more than 300, I type the condition in the cell D2 and then use this cell reference in the formula like this:

=COUNTIF(B2:B11, D2)

Or we can even write the logical operator in the quotation marks inside the formula and use & to reference the cell D3 like this:

=COUNTIF(B2:B11, ">"&D3)

Count cells using cell reference in COUNTIF() function. Image by Author.

Counting blank and non-blank cells

Missing data can be just as important as the data you have. And COUNTIF() helps us spot those gaps. Let's say I’m managing inventory and want to know how many fruits are out of stock (blank cells). For this, I create the following formula:

=COUNTIF(B2:B11,"")

And to count how many fruits are available (non-empty cells), I use this formula:

=COUNTIF(B2:B11, "<>")

However, if your cell contains any invisible text like space or any special character, COUNTIF() will count them as non-blank cells.

Count blank and non-blank cells using COUNTIF() function. Image by Author.

Counting dates based on different conditions   

We can also count dates based on different conditions. Let’s say we want to count how many employees were hired on the date in cell B11 (1 March 2024). So our formula would be:

=COUNTIF(B2:B9, B11)

And to check who was hired on or before the date in cell B11, I use this formula:

=COUNTIF(B2:B9, ">="&B11)

Count difference in dates using COUNTIF() function. Image by Author.

COUNTIF() with multiple criteria 

We can also use COUNTIF() with multiple criteria. If I want to count how many dates fall between January 1, 2024, and January 30, 2024, I type:

=COUNTIF(B2:B9, ">="&B12) - COUNTIF(B2:B9, ">"&B13)

Alternatively, you can use the COUNTIFS() function for multiple criteria like this:

=COUNTIFS(B2:B9,">="&B12,B2:B9,"<="&B13)

Both will give the same result. 

Use COUNTIF() for two conditions. Image by Author.

Excel COUNTIF() vs COUNTIFS()

Excel's COUNTIF() function counts cells based on a single criterion, as we have seen in the examples, above, and we can even filter multiple conditions by using more than one COUNTIF(), as we saw in that last example.

Now is a good time to say that, if your data does require multiple criteria, Excel provides an extended and newer function called COUNTIFS(). While COUNTIFS() behaves exactly like COUNTIF() when only one condition is specified, it also lets you apply more than one condition.

Despite COUNTIFS() being newer and more capable with AND conditions, COUNTIF() still has its place in Excel. It often creates more readable formulas for simple conditions, making your spreadsheets easier to understand and maintain. More importantly, unlike COUNTIFS(), COUNTIF() handles OR conditions, where you want to count cells that meet any one of several criteria.

Let’s understand this with a simple example: 

=COUNTIF(B2:B11, "100") + COUNTIF(B2:B11, "900")

This formula will count cells where the value is either 100 or 900. Since no 900 is in the cells and only one 100 is present, it will show 1 as a result.

Applying OR logic using COUNTIF(). Image by Author. 

Suppose, I also have to know how many times a value has 300 and kiwi in the same row. For this, I can use COUNTIFS():

=COUNTIFS(B1:B11, "300", A1:A11, "kiwi")

The formula will count the rows where both conditions are met. However, it gives 0 because neither of the conditions was met. 

Applying AND logic using COUNTIFS(). Image by Author. 

This distinction is helpful to know, which is why we included in our Excel formulas cheat sheet. And if you really want to stay current with the latest developments, I recommend taking our Excel Fundamentals skill track.

Common Errors and Troubleshooting

Even the best Excel users sometimes run into COUNTIF() problems. The function is helpful but has a few quirks that can trip you up. Here are the most common issues people face and how to solve them quickly.

Incorrect count for long text strings

COUNTIF() doesn't support text strings longer than 255 characters. If you work with longer chunks of text and get unexpected results, try using CONCATENATE() or the & operator to break down and combine your criteria into manageable form: 

=COUNTIF(range, CONCATENATE("First part of text", "Second part of text"))

Or

=COUNTIF(range, "First part of text" & "Second part of text")

VALUE! error in COUNTIF()

A #VALUE! error in COUNTIF() often happens when the formula references a closed workbook. This happens because Excel does not allow COUNTIF() to count data from an external file unless the file is open. To solve this, make sure the referenced workbook is open before running the formula. The error should disappear once the file is open, and COUNTIF() will work properly.

Incorrect use of operators

Sometimes we may forget to enclose logical operators in quotation marks. Always make sure operators like >, <, and <> are inside quotes in your criteria. Otherwise you won’t get the output.

 or example, the formula below will show an error:

=COUNTIF(B2:B10,>500)

To avoid this, make sure the logical operator is enclosed in the quotation marks. 

=COUNTIF(B2:B10,">500")

Excel COUNTIF() Best Practices

Here are some key practices to follow when you work with COUNTIF() in Excel. 

• Use named ranges: Instead of selecting a range like A2:A10 in your formula, give a customized name to the range to make the formula easier to understand. For example, to count how many times Banana appeared in a list, we’d use =COUNTIF(A2:A10, "Banana"). But if I name the range to Items and use the cell reference for the condition, I can update the formula like this: =COUNTIF(Items, C2). This way the formula is more clear and easier to understand. 

• Use COUNTIFS() for multiple conditions: Sometimes, we have to count data based on more than one condition. In such a case, instead of using multiple COUNTIF() formulas, use COUNTIFS() because it is the best choice for applying multiple filters.

COUNTIF() vs. Related Functions

Apart from COUNTIF() and COUNTIFS(), we have a lot of other Excel functions to count cells. However, each function has a specific purpose. So, let’s take a closer look at these:

Just like many other Excel functions, COUNTIF() is also one of the most helpful ways to count and filter data. Instead of manually going through your list, you can use this function to find what you need instantly. And the best part is that it works with text, numbers, blank cells, and patterns, which makes data analysis easier. 

If you want to learn more, start with our Introduction to Excel course to learn the essentials. For a strong foundation, the Excel Fundamentals skill track will take you through all the core concepts step by step. Finally, if you work with financial data, our Financial Modeling in Excel course will guide you on building professional financial models and forecasts.