COUNTIF in Excel: Formula, Examples and Tips
COUNTIF counts the cells in a range that meet a condition. Here are the formulas you will use most, with a small table to test them.
The data used in the examples
| A: Region | B: Rep | C: Amount | |
|---|---|---|---|
| 1 | Region | Rep | Amount |
| 2 | East | Ann | 1200 |
| 3 | West | Bob | 800 |
| 4 | East | Cara | 450 |
| 5 | North | Ann | 950 |
| 6 | West | Dan | 600 |
| 7 | East | Bob | 300 |
COUNTIF syntax
=COUNTIF(range, criteria) counts how many cells in the range match the criteria.
| Goal | Formula | Result |
|---|---|---|
| Rows for East | =COUNTIF(A2:A7,"East") | 3 |
| Amounts over 500 | =COUNTIF(C2:C7,">500") | 4 |
| Amounts of exactly 800 | =COUNTIF(C2:C7,800) | 1 |
| Names that start with B | =COUNTIF(B2:B7,"B*") | 2 |
| Blank cells in a range | =COUNTIF(A2:A7,"") | 0 |
COUNTIFS: more than one condition
=COUNTIFS(A2:A7,"East",C2:C7,">400") counts East rows over 400. In the sample that is 2 (1,200 and 450).
Find duplicates with COUNTIF
Put =COUNTIF($B$2:$B$7,B2) next to each name. Anything above 1 appears more than once. In the sample, Ann, Bob and the rest: Ann is 2, Bob is 2, Cara is 1, Dan is 1. To remove them, see how to remove duplicates in Excel.
Common mistakes
- Forgetting quotes around operators: write “>500”, not >500.
- COUNTIF is not case sensitive. “east” and “East” count the same.
- Wildcards: * matches any text and ? matches one character. To count a real asterisk, use ~*.
- COUNTIF counts text that looks like a number differently from true numbers. Keep your data types consistent.
To add up values instead of counting rows, use SUMIF and SUMIFS.
Free pricing tips for your business
Get short, practical tips on pricing jobs and using our free calculators. No spam, unsubscribe any time.
Frequently asked questions
How do I count cells that are not blank?
Use =COUNTIF(A2:A7,”<>“) or the COUNTA function.
How do I count cells between two numbers?
Use COUNTIFS with two conditions: =COUNTIFS(C2:C7,”>=500″,C2:C7,”<=1000").
Is COUNTIF case sensitive?
No. Use SUMPRODUCT with EXACT if you need a case-sensitive count.
Can COUNTIF count across sheets?
Yes. Add the sheet name to the range, for example Sheet2!A2:A100.
Need a custom Excel sheet or automation?
Talk to usMore free tools: All free tools · SUMIF and SUMIFS · Remove duplicates · Conditional formatting