Excel COUNTIF(): How to Use the Excel COUNTIF() Function

The Excel COUNTIF() function is a statistical function that is used to count the number of cells that meet specific criteria. For example, the function can be used to count how often a specific location appears in a list of sales. In this tutorial, you’ll learn how to use the Excel COUNTIF() function and how to customize it to meet your needs. You will learn all the basics of the function and even dive into some advanced uses of the function.

By the end of this tutorial, you’ll have learned:

• How to understand and how to use the Excel COUNTIF() function
• How to resolve common problems that occur when using the Excel COUNTIF() function
• What some of the best practices are when working with the COUNTIF() function
• How to dive deeper into the function using custom use cases of the function

Understanding the Excel COUNTIF() Function

Let’s take a look at the Excel COUNTIF() function and its arguments to better understand how to use it:

=COUNTIF(range, criteria)

As you can see from the code block above, the function takes two arguments. Let’s take a closer look at them in the table below:

Argument	Description	Required?
range=	The range of cells to apply the condition to	Yes
criteria=	The condition to apply, including any of the logical operators that may be required	Yes

On the surface, the function may not seem incredibly intuitive. You can think of the arguments as below:

1. The range represents the values to count, and
2. The criteria represent what to look for.

The criteria can be thought of as either logical, wildcards, or partial matches. This is extremely powerful, allowing you to customize the function quite a bit. For example, you can search for a cell that contains an exact string, or even just a partial string. For example, you can use wildcard operators to search for a text string starting with “North”, but could be either “North-West” or “North-East”.

Let’s take a look at how some of these criteria work in the Excel COUNTIF() function:

1. Logical operators can check for a logical condition, such as if an item is equal to, less than, and more. These operators include: <, >, <=, >=, and =.
2. Wildcard operators let you use characters to represent different types of strings. For example, the ? character represents any single character. The * character, on the other hand, represents any number of any character.

Now that we’ve covered a lot of the theory of the function, let’s dive into an example of how you can use the function.

How to Use the Excel COUNTIF() Function

Using the Excel COUNTIF() function can feel a little unnatural at first. This is especially true because there are a number of different options for how to search for different criteria. In this section, we’ll take a look at a simple example first and then dive into some other use cases of how to apply different criteria.

Let’s break down what we’re doing in the example above:

1. We have a list of sales, broken out by regions in columns A and B.
2. We want to count the number of sales (meaning how often each region is represented) in columns E and F.
3. In cell F3, we’re hoping to count how often the string of text "North" appears in the range B3:B13.
4. We use the following formula =COUNTIF($B$3:$B$13, E3). The function search across the absolute range of B3:B13 for the string that’s contained in cell E3. In this case, that’s North, which appears three times.

Now that we’ve walked through a simple example, let’s take a look at some of the different criteria options available.

Understanding Different Criteria Options in the Excel COUNTIF() Function

The Excel COUNTIF() function provides a large variety of criteria options. Earlier, you learned that you can use logical and wildcard operators. This can include dates and cell references, as well.

The table below breaks down the different options available to you in searching for different criteria in the COUNTIF() function:

Criteria Logic	Excel Criteria
Cells equal to “North”	“North”
Cells not equal to “North”	“<>North”
Cells starting with “North”	“North*”
Cells ending with “West”	“*West”
Cells greater than 1000	“>1000”
Cells greater than or equal to 1000	“>=1000”
Cells less than 1000	“<1000”
Cells less than or equal to 1000	“<=1000”
Cells equal to 1000	1000
Cells less than the value in A2	“<“&A2
Cells greater than today’s date	“<“&TODAY()
Cells not equal to today’s date	“<>”&TODAY()

While a lot of these probably make sense, some of them may stick out as odd. For example, when you’re using a function or a cell reference in your criteria, be sure to wrap the operator in a string. When you do this, you need to concatenate the string using the ampersand operator.

In the following section, you’ll learn how to tackle common problems when working with the COUNTIF() function.

Common Problems When Working with Excel COUNTIF()

When working with the COUNTIF() function, you may encounter some problems. There are three main types of problems that you may run into when working with the function:

1. Incorrect counts returned for long strings. This error occurs when strings exceed 255 characters. Excel works best when the string is under that character limit.
2. No counts are returned when there should be. In order to resolve this, you should check if your conditions are wrapped in quotes, in order to close off the condition.
3. A #VALUE error is returned. If you’re referencing a value in another workbook, you need to make sure that the workbook is open. Excel expects that the workbook is open to be able to search within it.

The list above covers the main causes of problems when working with the function. Writing the criteria can be a bit awkward and can take some getting used to. Refer back to the table above to see how best to write your criteria.

Best Practices When Working with Excel COUNTIF()

In this section, we’ll explore some of the best practices when working with the COUNTIF() function. You can also think of some of these as tips and tricks that simply make the function more intuitive. Let’s dive right in!

COUNTIF() is Case Insensitive

The COUNTIF() function ignores casing, meaning that it doesn’t matter if your text is uppercase or lowercase. This can lead to some unexpected behavior, but can actually also make your function much simpler to use! For example, if you’re searching for North, it doesn’t matter if you search for NORTH, North, north, or even NoRtH! When you’re working with text data, the values may have some data entry errors. Because of this, text insensitivity can actually be quite helpful.

Named Ranges Make COUNTIF() More Intuitive

Using named ranges can make working with the function significantly easier. Rather than using cell references and needing to worry about absolute references, using named ranges can make the function much simpler. If you think of the function of asking Excel to look in a certain range for certain criteria, this makes even more sense. Keep in mind that you’re also writing your function to be easily understood by future users of your workbook.

COUNTIF() Works with “?” and “*” Wildcards

Using wildcards can make your criteria much stronger. Excel provides two wildcards that you can use in the COUNTIF() function: ? replaces any single character, while * replaces any number of characters. Knowing this can make counting different string values that much easier.

Additional Resources for the Excel COUNTIF() Function

• How to Count Not Blank Cells in Excel with COUNTIF()
• How to Count Cells That Contain Text in Excel

Want to dive into the official documentation for the Excel COUNTIF() function? Check it out here.