fx
FunctionPedia
Quick Answer

COUNTIF Function

COUNTIF counts cells that match a condition. Give it a range and a criterion (like '>100' or 'East') and it tells you how many cells pass the test.

✓ Excel✓ Google SheetsExcel All versions

Syntax

COUNTIF
(range, criteria)

Parameters

ParameterDescriptionRequired
rangeThe range of cells to count. Each cell is evaluated against the criteria.Required
criteriaThe condition that determines which cells to count. Can be a number, text, comparison, or wildcard pattern.Required

Basic Example

Count how many students scored 80 or above

=COUNTIF(A2:A30, ">=80")
Result12

The formula checks each cell in A2:A30 and counts how many contain a value of 80 or higher.

Advanced Examples

Example 1: Count occurrences of specific text

Survey analysis

Count how many responses say 'Yes'

=COUNTIF(B2:B100, "Yes")
Result: 67
Exact text match (case-insensitive). Counts all cells containing exactly 'Yes' (or 'yes', 'YES').

Example 2: Count with wildcard for partial text

Data analysis

Count cells containing 'error' anywhere in the text

=COUNTIF(A2:A50, "*error*")
Result: 5
The asterisks match any surrounding text. '*error*' catches 'Error found', 'Type error', 'Syntax error message'.

Example 3: Count non-blank cells

Data quality

Count how many cells in a range are not empty

=COUNTIF(A2:A100, "<>")
Result: 85
"<>" (not equal to empty) counts all cells that have any content — numbers, text, or formulas.

How COUNTIF Works

COUNTIF scans each cell in the range and checks it against your criteria. For exact text matches, it does a case-insensitive comparison. For numeric criteria like '>100', it evaluates the comparison operator. For wildcards, * matches any sequence of characters and ? matches exactly one character. Each cell that passes the test increments the count by 1. The result is a single number: the total count of matching cells.

1
Select the range
Choose the cells you want to count through (e.g., A2:A50).
2
Define the criteria
Specify what to count: a number (5), text ('Yes'), comparison ('>=80'), or wildcard ('*error*').
3
Enter the formula
Type =COUNTIF(range, criteria) and press Enter.

Important Notes & Limitations

  • COUNTIF handles only one condition. For multiple conditions, use COUNTIFS.

  • Text criteria are case-insensitive — 'yes' and 'YES' both match.

  • COUNTIF cannot count cells by color, font, or other visual formatting.

  • For counting unique values, COUNTIF alone isn't sufficient — combine with UNIQUE or advanced formulas.

Common Errors & Fixes

0 result (criteria not matching)The criteria text/number doesn't exist in the range, or there's a formatting mismatch

Fix: Double-check that the values in the range actually match your criteria. Watch for trailing spaces.

Unexpected count (text vs number)Numbers stored as text won't match numeric criteria like '>100'

Fix: Convert text numbers to actual numbers, or use text criteria: =COUNTIF(A2:A50, ">100") may not work on text-formatted numbers.

Wrong count with wildcardsWildcard criteria treating numbers as text

Fix: Wildcards only work on text content. Numeric cells won't match wildcard patterns.

Download Practice File

Practice COUNTIF with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the COUNTIF function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

SUMIF with a Date Range

SUMIF only takes one condition, so summing between two dates needs a trick. Learn three clean ways to SUMIF (or SUMIFS) over a date range in Excel and Google Sheets.

Frequently Asked Questions

What's the difference between COUNTIF and COUNTIFS?
COUNTIF uses one condition. COUNTIFS uses multiple conditions: =COUNTIFS(A2:A50, "East", B2:B50, ">100") counts rows where both A is 'East' AND B is >100.
Can COUNTIF count blank cells?
Yes. Use =COUNTIF(A2:A50, "") to count empty cells, or =COUNTIF(A2:A50, "<>") to count non-empty cells.
How do I count unique values?
In Excel 365, use =COUNTA(UNIQUE(A2:A50)). In older Excel, use =SUMPRODUCT(1/COUNTIF(A2:A50, A2:A50)) for unique counts.
Can COUNTIF count by cell color?
No. COUNTIF cannot filter by background or font color. You need VBA or manual filtering for color-based counting.