How to Use COUNTIF in Google Sheets
COUNTIF counts only the cells that meet a condition. The syntax is =COUNTIF(range, criterion) — like counting how many orders came from one region. Here is exactly how it works, with wildcards, counting duplicates, and COUNTIFS.
COUNTIF counts only the cells that meet a condition you set — not every cell, just the ones that match. How many orders came from the North region? How many scores were above 80? How many times does one name appear in a list? COUNTIF answers each with a single, readable formula in Google Sheets.
The COUNTIF syntax, explained
=COUNTIF(range, criterion)| Part | Meaning |
|---|---|
| range | The cells to check against your condition |
| criterion | The condition to match, like "North" or ">80" |
A real example
Say column A holds regions in the range A2:A100. To count how many rows are from the North region, you write:
=COUNTIF(A2:A100, "North")This checks every cell in A2:A100 and returns how many equal "North". Wrap text criteria in quotation marks. You can also test numbers with an operator — =COUNTIF(B2:B100, ">80") counts every value greater than 80. Keep the operator inside the quotes, together with the number.
Partial matches with wildcards
COUNTIF understands two wildcards in text criteria: an asterisk (*) for any number of characters and a question mark (?) for a single character. To count every product code that starts with "AB":
=COUNTIF(A2:A100, "AB*")A lone asterisk, =COUNTIF(A2:A100, "*"), counts every cell that contains text — a quick way to count non-blank labels while ignoring numbers and empty cells.
Counting duplicates with COUNTIF
One of the most useful jobs for COUNTIF is spotting repeats. In a helper column next to your data, count how many times each value appears in the whole column:
=COUNTIF(A:A, A2)Any result above 1 means the value is a duplicate. Copy the formula down and you can instantly see which entries repeat. To highlight them instead, use conditional formatting with a custom COUNTIF rule, and when you are ready to clean the list, see how to remove duplicates in Google Sheets.
More than one condition: COUNTIFS
When you need two or more conditions — say the North region AND sales over 100 — use COUNTIFS. It takes range and criterion in pairs:
=COUNTIFS(A2:A100, "North", B2:B100, ">100")COUNTIF pairs naturally with other tools. Add up the matching values instead of counting them with SUMIF, build extra logic around a count with an IF function, or do the same task in Excel with COUNTIF in Excel.
Frequently asked questions
What is the difference between COUNT, COUNTA, and COUNTIF?
COUNT counts only cells that contain numbers. COUNTA counts every non-empty cell, numbers and text alike. COUNTIF counts cells that meet a condition you set. Use COUNTIF when you need to count by a rule rather than count everything.
How do I count duplicates in Google Sheets with COUNTIF?
In a helper column, use =COUNTIF(A:A, A2) and copy it down. Any result greater than 1 means that value appears more than once. To count only the extra copies, subtract 1 from each result.
Can COUNTIF use two conditions?
No — COUNTIF handles a single condition. For two or more conditions that must all be true, use COUNTIFS, which takes range and criterion in pairs, like =COUNTIFS(A2:A100, "North", B2:B100, ">100").
Is COUNTIF in Google Sheets the same as in Excel?
Yes. The syntax and wildcards work the same way in both, so =COUNTIF(A2:A100, "North") behaves identically. Google Sheets calls the second argument the criterion, while Excel calls it the criteria, but they do the same job.