How to Use COUNTIF in Excel: Syntax and Practical Examples

COUNTIF counts cells in one range that meet one condition. Use it for questions such as “How many orders are late?”, “How many scores are at least 80?”, or “How many cells contain a word?”

COUNTIF syntax

=COUNTIF(range, criteria)

• range is the group of cells to test.
• criteria is the condition, such as "Apple", ">=80", or a cell reference.

Depending on regional settings, Excel may use a semicolon instead of a comma as the argument separator.

Common COUNTIF examples

Use operators with cell references

When a comparison operator is combined with a cell reference, join them with &:

=COUNTIF(C2:C20,">"&E2)

This counts values in C2:C20 that are greater than the value in E2. Writing ">E2" would look for text rather than use E2's value.

Count text with wildcards

• * matches any number of characters.
• ? matches one character.
• ~ before * or ? treats that symbol literally.

COUNTIF is not case-sensitive. If letter case must matter, use a case-sensitive formula built with EXACT and SUMPRODUCT instead.

One condition versus multiple conditions

COUNTIF applies one condition to one range. Use COUNTIFS when the same row must satisfy multiple conditions. For example, this counts rows where column B is Apple and column C is greater than 6:

=COUNTIFS(B2:B20,"Apple",C2:C20,">6")

Adding two COUNTIF results represents an OR-style total, but overlapping conditions can double-count the same cell. Review the logic before combining formulas. See how to use COUNTIFS in Excel for multi-condition examples.

Count unique values

COUNTIF does not directly return a distinct count. In current Microsoft 365 versions, a straightforward formula for nonblank unique values is:

=COUNTA(UNIQUE(FILTER(A2:A100,A2:A100<>"")))

Older Excel versions require a different array or PivotTable approach. Choose the method supported by your version.

Why COUNTIF returns an unexpected result

• Numbers may be stored as text.
• Text can contain leading, trailing, or nonbreaking spaces.
• The operator may be missing quotation marks.
• A wildcard may match more text than expected.
• The workbook may be in manual calculation mode.

Check a few source cells directly and use Excel's formula-evaluation tools. For related basics, see essential Excel functions.