How to Use COUNTIF in Excel: Text, Numbers, Wildcards, Dates and Blanks

shape
shape
shape
shape
shape
shape
shape
shape
How to Use COUNTIF in Excel: Text, Numbers, Wildcards, Dates and Blanks - featured image

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

  1. The syntax
  2. Example data
  3. Count an exact text
  4. Count numbers and comparisons
  5. Use a cell in the comparison
  6. Wildcards for partial matches
  7. Count blank and non-blank cells
  8. Count dates
  9. Find duplicates
  10. Count unique values
  11. COUNTIF with another sheet or a named range
  12. COUNTIF limits and when to use COUNTIFS or SUMPRODUCT
  13. Example: an order status report
  14. Percent of rows that meet a condition
  15. Common mistakes
  16. Practice problems
  17. 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.

FruitSold
Apple40
Banana55
Apple70
Cherry85
Banana55
Apple90
Mango20
Avocado65

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,””).

Related guides and free tools

Leave a Reply

Your email address will not be published. Required fields are marked *