COUNTIF counts the cells in a range that meet one condition. It answers questions such as how many orders are marked Paid, how many students scored above 60, how many names start with the letter A, or how many dates fall after today. It is one of the first functions worth learning, because counting is part of almost every report. This guide explains the syntax, shows how to count exact text, numbers and comparisons, how to use wildcards for partial matches, how to count blanks and non-blanks, how to count dates, how to find duplicates and unique values, and how to avoid the common errors. A small example list with numbers you can check by hand is used throughout.
Updated November 2026
In this guide
- The syntax
- Example data
- Count an exact text
- Count numbers and comparisons
- Use a cell in the comparison
- Wildcards for partial matches
- Count blank and non-blank cells
- Count dates
- Find duplicates
- Count unique values
- COUNTIF with another sheet or a named range
- COUNTIF limits and when to use COUNTIFS or SUMPRODUCT
- Example: an order status report
- Percent of rows that meet a condition
- Common mistakes
- Practice problems
- Quick recap
The syntax
COUNTIF has two arguments: COUNTIF(range, criteria). The range is the group of cells to check, and the criteria describe which cells to count. The criteria can be a number, a text, a cell reference, an expression such as “>60”, or a pattern with wildcards. COUNTIF is not case sensitive, so apple and Apple are treated as the same. It counts the cells that meet the criteria and returns a number. If no cell matches, the result is 0. To count with more than one condition, use COUNTIFS, as in our COUNTIFS guide.
Example data
Use a list of eight rows. Column A holds fruit names, and column B holds a number of items sold.
| Fruit | Sold |
|---|---|
| Apple | 40 |
| Banana | 55 |
| Apple | 70 |
| Cherry | 85 |
| Banana | 55 |
| Apple | 90 |
| Mango | 20 |
| Avocado | 65 |
Apple appears 3 times, Banana 2 times, and Cherry, Mango and Avocado once each. The numbers add up to 480.
Count an exact text
To count how many rows are Apple, use =COUNTIF(A2:A9,"Apple"). The result is 3. Text criteria need quotation marks, unless you use a cell reference. If cell D2 contains the word Banana, =COUNTIF(A2:A9,D2) returns 2. A cell reference is better, because you can change the word without editing the formula. COUNTIF ignores upper and lower case, so “apple” gives the same result of 3.
Count numbers and comparisons
To count the cells equal to 55, use =COUNTIF(B2:B9,55), which returns 2. For a comparison, put the operator in quotation marks with the number: =COUNTIF(B2:B9,">60") counts the values above 60, which are 70, 85, 90 and 65, so 4. Use “<50” for below 50, which gives 2, since 40 and 20 qualify. Use “>=55” for 55 or more, which gives 6. Use “<>55” for everything except 55, which gives 6, since 8 minus 2 is 6.
Use a cell in the comparison
If the limit is stored in cell D2, join the operator and the cell with an ampersand: =COUNTIF(B2:B9,">"&D2). If D2 holds 60, the result is 4. Changing D2 to 80 gives 2, because only 85 and 90 are above 80. This lets users change the limit without touching the formula. A common mistake is to write ">D2" in quotes, which Excel reads as the text D2 and not as the cell, so the formula returns 0.
Wildcards for partial matches
COUNTIF supports two wildcards. The asterisk stands for any number of characters, and the question mark stands for exactly one character. To count the fruit names that start with A, use =COUNTIF(A2:A9,"A*"). The three Apple rows and the Avocado row qualify, so the result is 4. To count the names that contain the letters an, use =COUNTIF(A2:A9,"*an*"). Banana twice and Mango match, so the result is 3. To count five-letter names, use “?????”, which gives 4, for Apple three times and Mango once. Use a tilde before an asterisk to count a real asterisk.
Count blank and non-blank cells
To count the empty cells in a range, use =COUNTIF(A2:A20,""). If only 8 of the 19 cells contain data, the result is 11. To count cells that are not empty, use =COUNTIF(A2:A20,"<>"), which returns 8. COUNTA gives the same non-blank count. Be careful with cells that look blank but contain a space or a formula that returns an empty text, since COUNTIF with “” counts the empty text, but not the cells with a space. Clean the data first, as in our remove spaces guide.
Count dates
Dates are numbers, so the same operators work. To count dates after today, use =COUNTIF(C2:C20,">"&TODAY()). To count dates in a month, use two criteria with COUNTIFS, such as dates on or after 1 March and before 1 April. To count a specific date, put the date in a cell and use that cell as the criteria. For example, if D2 holds 15 March 2027, =COUNTIF(C2:C20,D2) counts the rows with that exact date. Make sure that the dates are real dates, not text, or the count will be wrong.
Find duplicates
To flag repeated values, add a helper column with =COUNTIF($A$2:$A$9,A2). For our list, the three Apple rows show 3, the two Banana rows show 2 and the others show 1. A value above 1 is a duplicate. You can use =IF(COUNTIF($A$2:A2,A2)>1,"Duplicate","") to mark only the second and later copies. Conditional formatting with the same idea highlights duplicates, and our remove duplicates guide shows how to delete them.
Count unique values
To count how many different fruit names there are, use =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9)). Each value contributes 1 divided by the number of times it appears, so the three Apple rows give three times one third, which is 1, and so on. The total is 5, for Apple, Banana, Cherry, Mango and Avocado. In Excel 365, =COUNTA(UNIQUE(A2:A9)) gives the same result and is easier to read. The SUMPRODUCT version works in all versions, but it fails if the range has blank cells.
COUNTIF with another sheet or a named range
The range can be on another sheet, such as =COUNTIF(Orders!B2:B500,"Paid"). It can also be a named range or a table column, such as =COUNTIF(Sales[Status],"Paid"). A table reference grows with your data, so the formula needs no update when you add rows. See our table guide and our named range guide. COUNTIF cannot use a range in a closed workbook, which causes a #VALUE! error, so open the source file first.
COUNTIF limits and when to use COUNTIFS or SUMPRODUCT
COUNTIF allows only one condition. For two or more conditions, use COUNTIFS. For an OR condition, add two COUNTIFs, such as =COUNTIF(A2:A9,"Apple")+COUNTIF(A2:A9,"Banana"), which gives 5. For case-sensitive counts or complex tests, use SUMPRODUCT, as in our SUMPRODUCT guide. COUNTIF also has a limit on text criteria of 255 characters, so a very long criteria text causes a #VALUE! error. For most uses, the plain function is enough.
Example: an order status report
Imagine an order list with 200 rows and a Status column that contains Paid, Open or Overdue. Three COUNTIF formulas build a summary: =COUNTIF(D2:D201,"Paid"), =COUNTIF(D2:D201,"Open") and =COUNTIF(D2:D201,"Overdue"). If the results are 150, 30 and 20, the three numbers add up to 200, which is a good check. The share of overdue orders is 20 divided by 200, which is 10 percent. Put the results in a small table beside a pie chart, and the report is ready. The same pattern works for any status, category or grade column.
Percent of rows that meet a condition
To show the share of rows that meet a condition, divide COUNTIF by the total count. With our example list, the share of values above 60 is =COUNTIF(B2:B9,">60")/COUNTA(B2:B9), which is 4 divided by 8, or 50 percent. Format the cell as a percentage. This is a quick way to report pass rates, completion rates and response rates. If the list can be empty, wrap the formula in IFERROR to avoid a division by zero. Our percentage of total guide explains the formatting and checks.
Common mistakes
The most common mistake is to forget the quotation marks around text or operators. Another is to write the operator and the cell together inside the quotation marks. A third is to use whole text numbers, such as numbers stored as text, which do not match. A fourth is to forget that wildcards work only with text criteria. A fifth is a range that does not line up with the data. A sixth is to expect case sensitivity. Test the formula on a small list, such as the one in this guide.
Practice problems
Try these on the example. Problem 1: count Banana, which is 2. Problem 2: count the values above 60, which is 4. Problem 3: count the names that start with A, which is 4. Problem 4: count the values between 50 and 80, using COUNTIFS, which gives 4, since 55, 70, 55 and 65 qualify. Problem 5: count the unique names, which is 5. Problem 6: count the rows that are Apple or Mango, which is 4.
Quick recap
COUNTIF(range, criteria) counts the cells that match one condition. Use quotation marks for text and operators, a cell reference with an ampersand for variable limits and wildcards for partial matches. Use “” for blanks and “<>” for non-blanks, date operators for dates and a helper column for duplicates. Count unique values with SUMPRODUCT or UNIQUE. Use COUNTIFS for several conditions. With these patterns, you can answer most counting questions in a few seconds.
Frequently asked questions
What is the COUNTIF formula?
=COUNTIF(range, criteria).
How do I count cells greater than a number?
Use =COUNTIF(range,”>60″) with the number you want.
Is COUNTIF case sensitive?
No.
How do I count cells that contain a word?
Use wildcards, such as “*word*”.
How do I count blank cells?
Use =COUNTIF(range,””).


