Grab our practice workbook πŸ‘‰ HERE and follow along:

How to use COUNTIF function in Excel

The syntax of the COUNTIF function is very simple. Here’s the basic formula:

= COUNTIF(range, criteria)
  • range” specifies the cells you want to examine, like A1:A10 in Excel.
  • criteria” describes what you’re searching for. It guides COUNTIF on which cells to include. This could be a number, text, a reference to another cell, or a formula.

Basic Example

Here’s a basic example of the Excel COUNTIF function. In the image below, we have a list of FIFA World Cup winners. Suppose we want to determine how many times Brazil has won the World Cup.

🀫 The correct answer is 5 times. Here is the formula with the COUNTIF function:

= COUNTIF(B5:B26,"Brazil")
Basic example

❗ Since our criterion (what we’re searching for) is text, we need to enclose it in quotation marks (“Brazil”). However, it’s not case sensitive, so both “Brazil” and “brazil” will give the same result.

If you’re searching for numbers, you don’t need to use quotation marks.

That’s a straightforward example, but COUNTIF can be applied in many different ways. Let’s explore some practical examples.

COUNTIF with Wildcard Characters for Partial Matches

COUNTIF is incredibly useful for counting variations of a specific word within your data. This can be achieved by using a wildcard character, allowing you to count cells that contain a certain word, phrase, or letters.

For instance, in our example, we note that Germany has won the World Cup multiple times. Initially, it was referred to as “West Germany” and later simply as “Germany.”

To count all of Germany’s victories, we’ll use the * wildcard. This character can be placed either at the start or end of your search term. Here, we’ll use it at the beginning to count any cells with “Germany” appearing at the end.

= COUNTIF(B5:B26, ”*germany”)

Another handy wildcard character for COUNTIF is the question mark (?). This is useful when you need to count cells that start or end with specific letters and have a precise number of characters.

For example, imagine we want to find out how many times a word with “r” in the second position shows up in the winner column.

= COUNTIF(B5:B26, ”?r*”)
  • ? represents any single character.
  • r is the character “r” we’re looking for in the second position.
  • * represents any number of additional characters after the β€œr”.
COUNTIF with wildcards

Similarly, if you’re trying to count how many cells end with “ly” and are exactly five characters long, you would use the following formula:

= COUNTIF(B5:B26, ”???ly”)
Use wildcards for partial match

Count Values that are Greater than or Less than a Specific Number

The COUNTIF function is also very useful with numbers. You can count cells that have values more or less than a certain number. Use logical operators like > (more than), < (less than), <> (not the same as), <= (less than or the same as), and >= (more than or the same as).

For example, if we need to count how many students got scores under 60.

= COUNTIF(B5:B17, ”<60”)
COUNTIF with logical operators

❗If your COUNTIF criteria is just a number, you don’t need quotes. But, if you’re using a logical operator (like >, <, <=, >=) with a number, you need to put both in quotes.

Here are some other helpful examples:

Equal to 9999
Less than or equal to 75β€œ<= 75”
Greater than or equal to 41β€œ>=41”
Not equal to 55β€œ<>55”

Count Values that are Greater than or Less than a Specific Date

Just like that, with COUNTIF you can count cells with dates before, after, or exactly the same as the date you choose.

Equal to March 1, 2024“3/1/2024”
Equal to or before April 25, 2024“<=4/25/2024”
Equal to or after August 15, 2024“>=8/15/2024”

Besides these typical uses, you can combine COUNTIF with Excel’s Date and Time functions, like TODAY(), to count cells based on today’s date.

For example, to count invoices due a week from today:

= COUNTIF(B5:B17, ”=”&TODAY()+7)
Count dates using TODAY function

Note: In this example today’s date was March 5, 2024.

❗In COUNTIF, logical operators are seen as text. So, you need to put the equals sign in quotation marks. Also, when linking a logical operator with an Excel function, use the ampersand (&) before the function.

The same rule applies when you reference a cell in the COUNTIF function. For example, to count invoices due on the date listed in cell D5:

= COUNTIF(B5:B17, "="&D5)
Count dates with COUNTIF

Count Cells that are Blank

You can also use COUNTIF to find how many cells are empty in a range of cells.

= COUNTIF(B5:B17, ””)
Count blank cells

❗Keep in mind, this method counts only completely empty cells. If a cell seems empty but has an invisible character (like a space), it won’t be counted as empty with this formula.

For a thorough method, particularly if you think some cells might have invisible characters, you should see how to use the TRIM function in Excel.

There’s also a specific function in Excel for counting empty cells: the COUNTBLANK function.

Count Non-Blank Cells

Similarly, you can create a COUNTIF formula that count cells that are not empty.

= COUNTIF(B5:B17, ”<>”&””)
Count non-blank cells with COUNTIF function

Limitations of COUNTIF

The COUNTIF function has a few limitations to keep in mind:

  • It is not designed to handle more than one condition at a time. For counting cells that meet several conditions, use the COUNTIFS in Excel.
  • COUNTIF is not case-sensitive. It does not differentiate between uppercase and lowercase letters.
  • The COUNTIF formula in Excel might give wrong results if you try to match strings that are longer than 255 characters.
  • It doesn’t count cells based on cell background or font color.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the Excel COUNTIF Function with hands-on examples. Download the workbook here and start applying what you’ve learned directly in Excel.

Excel Download Practice file

Featured Course

Excel Essentials for the Real World

Learn time-saving hacks to work smarter in Excel. Our members also consistently report a newfound enthusiasm for using Excel after taking our course.
Learn More

Leila Gharani

I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.

My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.