Math & aggregation
How to Use COUNTIF in Excel (Count with a Condition)
COUNTIF counts how many cells in a range meet a single condition: how many invoices say "Paid", how many scores are over 100, how many names start with S.
This guide covers text, numeric, and wildcard criteria, the common reasons COUNTIF returns 0, and when to move up to COUNTIFS for more than one condition.
COUNTIF syntax
=COUNTIF(range, criteria) | Argument | Required | What it does |
|---|---|---|
range | Required | The cells you want to count. |
criteria | Required | The condition: "Paid", ">100", "S*", or a cell reference like E1. |
Note: For more than one condition, use COUNTIFS. Works the same in Google Sheets.
Examples
Real, copy-paste-ready formulas.
1. Count by a text label
=COUNTIF(A:A, "Paid") Counts every cell in column A that exactly equals "Paid". Text criteria go in double quotes and are not case-sensitive.
2. Count values over a threshold
=COUNTIF(B:B, ">100") The comparison operator and number sit together inside one set of quotes. Use ">="&E1 to read the threshold from a cell.
3. Count with a wildcard
=COUNTIF(A:A, "S*") The * wildcard matches any number of characters, so "S*" counts every entry beginning with S. Use ? to match a single character.
How to write COUNTIF step by step
- 1
Click the result cell and type =COUNTIF(
- 2
Select the range of cells you want to count, then a comma.
- 3
Type the condition in quotes: "Paid", ">100", or "S*".
- 4
To compare against a cell, join the operator with &, e.g. ">"&E1.
- 5
Close the bracket ) and press Enter. For multiple conditions, use COUNTIFS instead.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
Result is 0 | The criteria does not match, usually a number stored as text or stray spaces in the data. | Confirm the range and criteria are the same data type; clean text with TRIM() and convert text-numbers to real numbers. |
Operator ignored | A comparison like >E1 was written without joining the cell reference. | Concatenate the operator and reference with &, e.g. ">"&E1. |
#VALUE! | COUNTIF references a closed workbook on another file. | Open the source workbook, or copy the data into the current file. |
#NAME? | The function name is misspelled, e.g. COUTIF. | Check the spelling: it is COUNTIF, with no space. |
Frequently asked questions
What is the difference between COUNTIF and COUNTIFS?
COUNTIF counts cells that meet one condition. COUNTIFS handles several conditions at once: =COUNTIFS(A:A, "West", B:B, "Paid") counts rows that are both West and Paid.
How do I count non-blank cells with COUNTIF?
Use =COUNTIF(A:A, "<>") to count cells that are not empty, or =COUNTA(A:A) which counts every non-blank cell including numbers and text.
Can COUNTIF use two criteria?
No. COUNTIF takes a single condition. For two or more conditions use COUNTIFS, which adds range/criteria pairs with AND logic.
Does COUNTIF work in Google Sheets?
Yes. The syntax is identical, including wildcards (* and ?) and comparison operators inside quotes.