COUNTIF Formula — Count Cells by Criteria in Excel & Sheets | SheetMaster
Counts the number of cells within a range that meet the given condition.
How to Use COUNTIF in Excel and Google Sheets
The COUNTIF function counts cells that meet a single condition — like how many orders exceed $1,000, how many tasks are marked "Complete," or how many entries fall within a date range. It is essential for data analysis, quality control, and reporting.
COUNTIF Syntax
=COUNTIF(range, criteria)
- range — The range of cells you want to count.
- criteria — The condition that determines which cells to count (text, number, expression, or cell reference).
Step-by-Step Example: Count Orders by Status
Suppose you have an order log:
| A (Order ID) | B (Status) | C (Amount) |
|---|---|---|
| 1001 | Completed | 1,200 |
| 1002 | Pending | 850 |
| 1003 | Completed | 2,300 |
| 1004 | Cancelled | 0 |
| 1005 | Completed | 950 |
| 1006 | Pending | 1,500 |
Goal: How many orders are "Completed"?
- Click cell E2 (or any empty cell).
- Enter:
=COUNTIF(B2:B7, "Completed") - Press Enter. The result is 3 (rows 1001, 1003, 1005).
To count "Pending" orders, change the criteria: =COUNTIF(B2:B7, "Pending") — returns 2. For a dynamic criteria, put "Completed" in cell F1 and use: =COUNTIF(B2:B7, F1).
Common COUNTIF Patterns
- Count exact text:
=COUNTIF(A:A, "Completed") - Count numbers greater than:
=COUNTIF(B:B, ">1000") - Count numbers less than or equal:
=COUNTIF(B:B, "<=500") - Count not equal to:
=COUNTIF(B:B, "<>Cancelled") - Count blank cells:
=COUNTIF(A:A, "") - Count non-blank cells:
=COUNTIF(A:A, "<>") - Count with cell reference:
=COUNTIF(B:B, ">"&E1)
Count Blank and Non-Blank Cells
Two essential patterns for data quality checks:
- Count truly blank cells:
=COUNTIF(A:A, "")— counts cells that are completely empty. - Count non-blank cells (cells with any value):
=COUNTIF(A:A, "<>")— counts cells that contain text, numbers, dates, or errors. Note: cells with spaces are not blank and will be counted. - Count cells that look blank but contain spaces:
=COUNTIF(A:A, " *")or combine with a helper column usingTRIM().
Using Wildcards with COUNTIF
Wildcards enable powerful partial matching:
*(asterisk) — matches any sequence of characters.=COUNTIF(A:A, "*East*")counts all cells containing "East" — including "Northeast" and "Eastern".?(question mark) — matches any single character.=COUNTIF(A:A, "???-100")matches "ABC-100" but not "AB-100".~(tilde) — escape wildcards.=COUNTIF(A:A, "~?")counts cells containing a literal question mark.- Count cells starting with:
=COUNTIF(A:A, "East*")counts cells that begin with "East". - Count cells ending with:
=COUNTIF(A:A, "*ing")counts cells ending with "ing".
COUNTIF with Date Criteria
COUNTIF works with dates just like numbers. Use DATE() or a cell reference:
=COUNTIF(A:A, ">="&DATE(2026,1,1))This counts all dates on or after January 1, 2026. For a date range, use COUNTIFS:
=COUNTIFS(A:A, ">="&E1, A:A, "<="&F1)To count dates from today backwards:
=COUNTIF(A:A, ">="&TODAY()-30)COUNTIF vs COUNTIFS: When to Use Each
| Feature | COUNTIF | COUNTIFS |
|---|---|---|
| Conditions | 1 | Up to 127 |
| Argument order | range, criteria | criteria_range1, criteria1, criteria_range2, criteria2, ... |
| OR logic across columns | Add two COUNTIFs: =COUNTIF(...)+COUNTIF(...) | Not natively supported (use COUNTIF + COUNTIF pattern) |
| Best for | Simple single-condition counts | Multi-condition counts (e.g., status + date range) |
Common COUNTIF Edge Cases
- Data type mismatch: Numbers stored as text won't match numeric criteria. Use
VALUE()or align data types. - Leading/trailing spaces: A cell with "Completed " (trailing space) won't match "Completed". Use
TRIM()on your data. - Case sensitivity: COUNTIF is not case-sensitive. "COMPLETED", "Completed", and "completed" all match the same count.
- Criteria length limit: COUNTIF criteria strings cannot exceed 255 characters. For longer patterns, use a helper column with
SEARCH()or switch to SUMPRODUCT. - Counting with OR logic: COUNTIF handles a single condition. For OR across values (e.g., count "Completed" OR "Pending"), add two COUNTIFs:
=COUNTIF(B:B, "Completed") + COUNTIF(B:B, "Pending").
Pro Tip: For multiple conditions on different columns, upgrade to COUNTIFS. The syntax is =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2). For example, count orders that are "Completed" AND over $1,000: =COUNTIFS(B:B, "Completed", C:C, ">1000"). COUNTIFS puts the sum_range first — opposite of SUMIF.
Common Errors & Fixes
COUNTIF returns 0 or wrong count
Causes:- Criteria not in quotes for text (e.g. "Yes" not Yes).
- Data type mismatch: numbers stored as text or vice versa.
- Extra spaces in cells; criteria does not match exactly.
Fixes:- Wrap text criteria in double quotes: "Completed", ">100".
- Use TRIM on data or match the stored format.
- For numbers, use "=100" or ">50" as the criteria string.
Frequently Asked Questions
Can COUNTIF handle multiple criteria?
No — COUNTIF handles only one condition at a time. For multiple criteria (e.g., "count rows where A>100 AND B=\"Yes\""), use COUNTIFS instead. To count with OR logic across different values, add two COUNTIFs together: =COUNTIF(A:A,"X")+COUNTIF(A:A,"Y"). For OR within the same column use the same approach: =COUNTIF(B:B,"Completed")+COUNTIF(B:B,"Pending").
Why does COUNTIF return 0 when I expect a count?
Check that your criteria match the data type (number vs text). Use quotes for text: "Completed" or "=100". For numbers use ">50" or "=100". Dates may need DATE() or a cell reference. Also watch for extra spaces — "Completed " won't match "Completed". Use TRIM() on your data to clean leading/trailing spaces.
How do I count blank or non-blank cells?
Use criteria "" for blanks: =COUNTIF(A:A,""). For non-blanks use "<>": =COUNTIF(A:A,"<>"). Note that cells containing spaces or formulas that return "" look blank but are NOT counted by the blank pattern. For truly empty cells only, use =COUNTBLANK(A:A).
What is the difference between COUNTIF and COUNTIFS?
COUNTIF has one condition and uses (range, criteria) syntax. COUNTIFS supports multiple conditions (up to 127) using (criteria_range1, criteria1, criteria_range2, criteria2, ...) syntax. Use COUNTIFS when you need to count only when two or more conditions are met across different columns.
Can COUNTIF use wildcards?
Yes. Use * for any sequence of characters, ? for a single character, and ~ to escape wildcards. Examples: =COUNTIF(A:A,"*apple*") counts cells containing "apple". =COUNTIF(A:A,"???-100") matches three-character prefixes like "ABC-100". =COUNTIF(A:A,"~*") counts literal asterisks.
How do I count cells with a date in a range?
Use criteria with DATE(): =COUNTIF(A:A,">="&DATE(2026,1,1)) counts dates on or after Jan 1, 2026. For relative dates: =COUNTIF(A:A,">="&TODAY()-7) counts entries in the last 7 days. For two-date ranges, use COUNTIFS: =COUNTIFS(A:A,">="&E1,A:A,"<="&F1).
How do I count cells that contain specific text (not exact match)?
Wrap the text with wildcards: =COUNTIF(A:A,"*specific text*") counts cells containing "specific text" anywhere. Use a cell reference: =COUNTIF(A:A,"*"&E1&"*") — typing the search term in E1 makes it reusable across multiple formulas.
Why is COUNTIF counting cells that don't appear to match?
Common hidden causes: (1) Numbers formatted as text that visually look like numbers — use ISNUMBER() to check. (2) Invisible characters from other systems — use TRIM() and CLEAN(). (3) Dates stored as text — use DATEVALUE() to convert. (4) COUNTIF is not case-sensitive, so "Yes" matches "yes" and "YES".
Related Formulas
Explore related formula generators to solve similar problems
🛠️ Related Tools
Want to become an Excel Pro?
Stop searching for formulas. Master Excel in 30 days with this top-rated course.
Learn More