SheetsdrillCOUNTIF
COUNTIF(range, criterion) counts how many cells in a range meet one condition, such as a value, a comparison like ">100" or a wildcard like "*gmail.com". Text matching ignores case, and blank cells are counted with "" or "<>" for not blank.
Syntax
=COUNTIF(range, criterion)| Part | What it is |
|---|---|
range | The cells to count, for example B2:B100 or a whole column B:B. |
criterion | The condition: "Yes", 5, E1, ">=10", "<>", "*text*". |
Examples
=COUNTIF(B2:B100, "Yes")Result: How many survey responses say Yes
=COUNTIF(C:C, ">1000")Result: The number of amounts above 1000
=COUNTIF(A:A, "*@gmail.com")Result: How many email addresses end in @gmail.com
Common mistakes
- Counting a number stored as text with a numeric criterion gives 0; convert the column or use the text version "5".
- COUNTIF(A:A, E1) works, but COUNTIF(A:A, ">E1") compares against the text E1; write ">"&E1.
- A criterion with a literal * or ? needs a tilde before it: "~*".
Practice COUNTIF
These drills check your answer on live data, free, no account needed.
- Count the survey responsesSome respondents typed a score, others typed "n/a" or "skip". In E1 count every response that was entered. In E2 count only the numeric scores.
Questions
How do I count cells that contain specific text?
Use wildcards: =COUNTIF(A:A, "*urgent*") counts cells containing urgent anywhere.
How do I count unique values?
COUNTIF cannot; use =COUNTUNIQUE(A2:A100).
Related
- COUNTIFSHow COUNTIFS in Google Sheets counts rows where several conditions are true, with examples for status and priority, date ranges and not-equal, plus errors.
- SUMIFHow SUMIF works in Google Sheets: add up values where another column matches text, a number, a date or a wildcard, with examples and the mistakes that give 0.
- UNIQUEHow the UNIQUE function in Google Sheets returns a list of distinct values or rows, how to sort and count them, and the errors people run into.
- AND and ORHow AND and OR work in Google Sheets to test several conditions at once, how to use them in IF, and why they fail inside FILTER and ARRAYFORMULA, with fixes.