COUNTIF in Google Sheets
Use COUNTIF when you want a count of the cells in a range that meet one condition — how many orders over $100, how many rows say "Done", and so on.
=COUNTIF(A2:A100, ">100")
Counts cells in A2:A100 whose value is greater than 100. The condition goes in quotes.
How it works
COUNTIF takes two things: the range to look in, and one criterion. It returns the number of cells in the range that satisfy the criterion. Text criteria and comparison operators (>, <, >=, <>) must be inside quotes; a plain number or a cell reference can be used directly.
Variations
Count an exact text match
=COUNTIF(A2:A100, "Done")
Not case-sensitive: "done" and "DONE" both count.
Count using a value from another cell
=COUNTIF(A2:A100, C1)
Compares against whatever is typed in C1.
Count cells that contain a word (wildcard)
=COUNTIF(A2:A100, "*urgent*")
* matches any run of characters, so this catches "Urgent!" and "very urgent".
Count non-blank cells
=COUNTIF(A2:A100, "<>")
<> on its own means "not empty".
Examples
| Scenario | Formula |
|---|---|
| Cells equal to 0 | =COUNTIF(A2:A100, 0) |
| Dates on or after today | =COUNTIF(A2:A100, ">="&TODAY()) |
| Cells greater than the value in B1 | =COUNTIF(A2:A100, ">"&B1) |
FAQ
What is the difference between COUNTIF and COUNTIFS?
COUNTIF checks one condition against one range. COUNTIFS lets you apply several conditions across several ranges at once.
Is COUNTIF case-sensitive?
No. To count with case sensitivity, use SUMPRODUCT with EXACT, e.g. =SUMPRODUCT(--EXACT(A2:A100,"Done")).
How do I count with two conditions on the same column?
Add two COUNTIFs, e.g. =COUNTIF(A2:A100,"apple")+COUNTIF(A2:A100,"pear"), or use COUNTIFS for an AND condition.