COUNTIF Formula Generator (2026) — Count Cells by Criteria | Excel & Sheets

Counts the number of cells within a range that meet the given condition.

Generated Formula
=COUNTIF(range, criteria)

Learning Resources

Want to master Excel? Check out this Top-Rated Course.

Mastering COUNTIF: Count Cells That Meet Any Condition

The COUNTIF function is essential for data analysis, quality control, and reporting. Need to know how many orders exceed $1,000? How many tasks are marked "Complete"? How many entries fall within a date range? COUNTIF answers these instantly.

COUNTIF Syntax & Common Patterns

The basic syntax: =COUNTIF(range, criteria)

  • Count exact text: =COUNTIF(A:A, "Completed")
  • Count numbers greater than: =COUNTIF(B:B, ">1000")
  • Count blank cells: =COUNTIF(A:A, "")
  • Count non-blank cells: =COUNTIF(A:A, "<>")
  • Count dates after a specific date: =COUNTIF(A:A, ">="&DATE(2026,1,1))

When COUNTIF Meets Wildcards

Wildcards make COUNTIF extremely powerful for partial matching. Use * for any sequence of characters and ? for a single character. For example, =COUNTIF(A:A, "*East*") counts all cells containing "East" — including "Northeast" and "Eastern".

Pro Tip: For multiple conditions, upgrade to COUNTIFS. The syntax is =COUNTIFS(criteria_range1, criteria1, criteria_range2, criteria2). It's like COUNTIF on steroids.

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, add two COUNTIFs together: =COUNTIF(A:A,"X")+COUNTIF(A:A,"Y").

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.

How do I count blank or non-blank cells?

Use criteria "" for blanks: =COUNTIF(A:A,""). For non-blanks use "<>": =COUNTIF(A:A,"<>").

What is the difference between COUNTIF and COUNTIFS?

COUNTIF has one condition; COUNTIFS supports multiple criteria ranges. Use COUNTIFS when you need to count only when two or more conditions are met.

Can COUNTIF use wildcards?

Yes. Use * for any characters and ? for one character. Example: =COUNTIF(A:A,"*apple*") counts cells containing "apple".

How do I count cells with a date in a range?

Use criteria with a date: =COUNTIF(A:A,">="&DATE(2025,1,1)) or =COUNTIF(A:A,">"&B1) where B1 holds the date.

Related Formulas

SUMIFPMTSUMIFSCOUNTIFSAVERAGEIF

Want to become an Excel Pro?

Stop searching for formulas. Master Excel in 30 days with this top-rated course.

Learn More