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” – the group of cells you want to check (e.g., A1:A10).
- “criteria” – What you’re counting: this can be a number, text, a cell reference, or a condition.
Basic Example
Let’s say you have a list of FIFA World Cup winners and want to find out how many times Brazil has won.
= COUNTIF(B5:B26,"Brazil")- Since “Brazil” is text, you must use quotation marks.
- COUNTIF is not case-sensitive, so “Brazil” and “brazil” return the same result.
- If counting numbers, quotation marks are not needed.
Brazil has won 5 times—but COUNTIF can do much more. Let’s explore practical ways to use it!
Featured Course
Excel Essentials for the Real World
COUNTIF with Wildcards: Count Partial Matches
Need to count words that partially match your data? Wildcards in COUNTIF make it easy.
Wildcards help when:
✔ You need to count variations of a word.
✔ You don’t know the exact text in a cell.
✔ You want to find patterns in your data.
Example: Counting Germany’s World Cup Wins
Germany has won multiple World Cups, but earlier wins were recorded as “West Germany”. To count all wins, use the asterisk (*) wildcard:
= COUNTIF(B5:B26, ”*germany”)- * matches any characters before “Germany,” so it includes both “West Germany” and “Germany.”
- Wildcards are not case-sensitive, so “germany” also works.
Using the Question Mark (?) for Precise Matches
The question mark (?) replaces a single character in a word.
For example, to count words where “r” is the second letter:
= 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”.
Want to count words exactly five characters long that end in “ly”? Use:
= COUNTIF(B5:B26, ”???ly”)- ??? = Any three characters.
- ly = The required ending.
Wildcards unlock powerful ways to search data in Excel. Let’s explore more advanced COUNTIF tricks!
COUNTIF Greater Than 0
You can easily use Excel’s COUNTIF function to find data that’s greater than zero.
Let’s say you’re tracking your daily expenses for a week. You want to count how many days you spent money (i.e., days where expenses were greater than zero).
Here’s how you can use the COUNTIF function to count if greater than 0.
=COUNTIF(B3:B9, ">0")
Count Values that are Greater than or Less than a Specific Number
Want to count how many values are higher or lower than a specific number? Excel’s COUNTIF function lets you filter data using logical operators.
How It Works:
You can use COUNTIF with:
- > (greater than)
- < (less than)
- >= (greater than or equal to)
- <= (less than or equal to)
- <> (not equal to)
Example: Counting Low Scores
If you need to count students who scored below 60, use this COUNTIF formula:
= COUNTIF(B5:B17, ”<60”)
Important Notes:
- If your criteria is just a number, no quotes are needed (e.g.,
=COUNTIF(B5:B17, 60)). - If you use a logical operator (
>,<,>=,<=), enclose both in quotes (e.g.,"<60").
More Examples:
| Condition | Criteria |
| Equal to 99 | 99 |
| 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
Need to count past or future dates in Excel? The COUNTIF function makes it easy. You can count dates that are before, after, or exactly on a given date.
How It Works:
Use COUNTIF with date conditions just like numbers:
| Condition | Criteria |
| Count dates equal to March 1, 2024 | “3/1/2024” |
| Count dates on or before April 25, 2024 | “<=4/25/2024” |
| Count dates on or after August 15, 2024 | “>=8/15/2024” |
Example: Counting Invoices Due in a Week
To count invoices that are due 7 days from today, use:
= COUNTIF(B5:B17, ”=”&TODAY()+7)How It Works:
- TODAY() → Returns today’s date.
- +7 → Adds 7 days to find invoices due in a week.
- “=”&TODAY()+7 → Ensures Excel reads the date correctly.
Note: In this example today’s date was March 5, 2024.
👉 Important: COUNTIF treats logical operators (>, <, =) as text, so you must put them in quotes. When using an Excel function inside COUNTIF, always join it with & (ampersand).
Using a Cell Reference for Dates
If the due date is stored in cell D5, you can reference it directly:
= COUNTIF(B5:B17, "="&D5)This approach makes your formula dynamic, updating automatically when the date in D5 changes.
Count Cells that are Blank
Need to find empty cells in Excel? Whether you’re checking for missing data or cleaning up a dataset, you can use COUNTIF to count blank cells in a range.
How to Use COUNTIF for Blank Cells
Use this formula to count empty cells:
= COUNTIF(B5:B17, ””)
Important Notes:
- This formula counts only completely empty cells.
- If a cell looks empty but contains spaces or invisible characters, it won’t be counted.
Dealing with Hidden Characters
Sometimes, cells appear blank but actually contain spaces or hidden characters. In such cases, COUNTIF won’t recognize them as empty.
👉 For a more thorough method, especially if you suspect hidden spaces, check out our guide on using the TRIM function in Excel.
Alternative: Using COUNTBLANK
Excel has a built-in function for counting blank cells:
=COUNTBLANK(B5:B17)This works the same as COUNTIF(B5:B17, “”), but it’s simpler and easier to read.
Count Non-Blank Cells
Similarly, you can create a COUNTIF formula that count cells that are not empty.
= COUNTIF(B5:B17, ”<>”&””)
Limitations of COUNTIF
The COUNTIF function is powerful, but it has some restrictions:
❌ Can’t count based on multiple conditions – Use COUNTIFS for that.
❌ Not case-sensitive – It treats “Excel” and “EXCEL” as the same.
❌ Can’t match text longer than 255 characters – Use SUMPRODUCT instead.
❌ Ignores cell formatting – It doesn’t count based on background or font color.
Download the Workbook
Want to master the COUNTIF function faster? Download our free Excel workbook and practice with real-world examples.
📥 Click here to download the workbook and start practicing now!
Featured Bundle
Black Belt Excel Bundle
Leila Gharani
I’ve spent over 20 years helping businesses use data to improve their results. I've worked as an economist and a consultant. I spent 12 years in corporate roles across finance, operations, and IT—managing SAP and Oracle projects.
As a 7-time Microsoft MVP, I have deep knowledge of tools like Excel and Power BI.
I love making complex tech topics easy to understand. There’s nothing better than helping someone realize they can do it themselves. I’m always learning new things too and finding better ways to help others succeed.
