The guide · COUNTIF
Syntax
=COUNTIF(range, criteria)- range: the cells to check, for example the Status column.
- criteria: what counts as a match. Text, a number, a comparison, or a cell.
Conditions you can use
| Criteria | Counts cells that… |
|---|---|
"Shipped" | say Shipped (not case-sensitive) |
">100" | are more than 100 |
">=100" | are 100 or more |
"<>Cancelled" | say anything except Cancelled |
"*ship*" | contain "ship" anywhere |
F1 | equal whatever is in F1 |
">"&F1 | are more than the number in F1 |
Why the quotes?
COUNTIF's condition is a piece of text that Excel reads for you. ">100" means "more than 100". Without the quotes, >100 isn't text or a number, so Excel can't read the formula at all. To compare with a cell, join the operator and the cell: ">"&F1. Writing ">F1" compares with the letters F1.
Common mistakes
- No quotes around the condition.
=COUNTIF(B2:B11,>100)breaks; write">100". - > when you mean >=. "More than 100" leaves out an order of exactly 100. "100 or more" includes it.
- The wrong column. Counting "Shipped" in the Amount column gives 0.
- Two conditions in one COUNTIF. COUNTIF takes one. For "Shipped and over 100", use
COUNTIFS.
FAQ
- How do I count cells with specific text in Excel?
- Use COUNTIF with the text in quotes, for example =COUNTIF(C2:C11,"Shipped"). It counts every cell in the range that says Shipped.
- How do I count cells greater than a number?
- Put the comparison in quotes: =COUNTIF(B2:B11,">100"). For 100 or more, use ">=100".
- Why does my COUNTIF return 0?
- Usually the range points at the wrong column, the text in the condition is spelled differently, or the cells contain extra spaces. Check one cell that should match against the condition.
- Is COUNTIF case-sensitive?
- No. "shipped", "Shipped" and "SHIPPED" all match the same cells.