Home › Excel functions › COUNTIF
COUNTIF Formula in Excel: How to Use It, with Examples
The COUNTIF formula counts how many cells in a range meet a condition. How many orders are shipped, how many scores are above 80, how often a name appears: COUNTIF answers all of these in one formula.
COUNTIF · In this guide
COUNTIF syntax
=COUNTIF(range, criteria)| Argument | What it means |
|---|---|
| range | The cells to check. |
| criteria | The condition a cell must meet to be counted, such as "Shipped", ">100" or a cell reference. |
Basic example: count orders by status
| A | B | C | |
|---|---|---|---|
| 1 | Order | Status | Amount |
| 2 | #1001 | Shipped | $120 |
| 3 | #1002 | Pending | $80 |
| 4 | #1003 | Shipped | $45 |
| 5 | #1004 | Cancelled | $200 |
| 6 | #1005 | Shipped | $60 |
| 7 | #1006 | Pending | $95 |
Three cells in B2:B7 say "Shipped", so the result is 3. COUNTIF isn't case-sensitive: "shipped" would give the same answer.
Types of criteria
| You want to count | Formula | Result |
|---|---|---|
| Exact text | =COUNTIF(B2:B7, "Pending") | 2 |
| Everything except | =COUNTIF(B2:B7, "<>Shipped") | 3 |
| Numbers above 100 | =COUNTIF(C2:C7, ">100") | 2 |
| Text ending in "ed" | =COUNTIF(B2:B7, "*ed") | 4 |
| Non-empty cells | =COUNTIF(B2:B7, "<>") | 6 |
| Empty cells | =COUNTIF(B2:B7, "") | 0 |
Operators go between quotes. The wildcard * matches any number of characters, ? matches one. In the fourth example, "Shipped" (×3) and "Cancelled" both end in "ed".
Use a cell as the condition
With a status typed in E2, this counts that status:
=COUNTIF($B$2:$B$7, E2)To combine an operator with a cell value, join them with &. With 100 in E3:
Find duplicates with COUNTIF
COUNTIF can tell you how often a value appears in a list. Put this next to each row and copy it down:
=COUNTIF($A$2:$A$100, A2)>1It returns TRUE for every value that appears more than once. You can use the same formula as a conditional formatting rule (Home › Conditional Formatting › New Rule › Use a formula) to highlight duplicates automatically.
COUNTIFS: multiple conditions
To count rows that meet several conditions, use COUNTIFS with pairs of range and criteria:
=COUNTIFS(B2:B7, "Shipped", C2:C7, ">100")Only order #1001 is both shipped and above $100, so the result is 1. All conditions must be true for a row to be counted.
Common COUNTIF mistakes
- The count is 0. Check for extra spaces in the data and the exact spelling of the criteria.
- Numbers aren't counted. Numbers stored as text don't match a numeric condition like
">100". - An operator with a cell doesn't work. Write
">"&E3, not">E3". - #VALUE! with long text. COUNTIF can't match strings longer than 255 characters.
- You need case-sensitive counting. COUNTIF can't do that. Use
=SUMPRODUCT(--EXACT(B2:B7, "Shipped"))instead.
Frequently asked questions
What's the difference between COUNT, COUNTA and COUNTIF?
COUNT counts cells with numbers, COUNTA counts all non-empty cells, and COUNTIF counts only the cells that meet a condition you set.
How do I count cells with an OR condition?
Add two COUNTIFs: =COUNTIF(B2:B7, "Shipped") + COUNTIF(B2:B7, "Pending") counts both statuses.
How do I count cells that contain a word?
Wrap the word in wildcards: =COUNTIF(A2:A100, "*invoice*") counts every cell that contains "invoice" anywhere.
Is COUNTIF case-sensitive?
No. To count with exact case, use SUMPRODUCT with EXACT.