COUNTIF Function

Counts the cells that meet one condition.

Category

Statistical

Available in

All versions, Microsoft 365

Returns

A whole number

Taught hands-on in Level 3: Intermediate Lookups & Data Summaries · Conditional Summaries module

Overview

COUNTIF counts the cells in a range that match a condition — a value, a comparison or a pattern.

Criteria can be a value ("North", 50000), a comparison in quotes (">=50000", "<>North"), a comparison joined to a cell (">="&F2), or a pattern with wildcards — * for any characters, ? for one character ("A*", "?d"). Text matching ignores upper/lower case.

Syntax

=COUNTIF(range, criteria) Explain

Arguments

ArgumentRequired?What it does
rangeYesThe cells to count.
criteriaYesThe condition, such as "North", ">=50000" or "A*".

Examples

Example 1 — Counting reps

Text, a comparison, "not equal" and a wildcard.

E2fx=COUNTIF(B2:B8,"North")
ABCDE
1RepRegionSalesMeasureResult
2AnaNorth62000North3
3BenSouth4850050k or more4
4CyNorth50000Not North4
5DiEast71250Name has an a2
6EdSouth39800
7FayNorth55400
8GusEast48500
=COUNTIF(B2:B8,"North") Explain

"*a*" matches Ana and Fay — matching ignores case.

Example 2 — Flag duplicates

Count how often each value appears in its own column; more than 1 means a duplicate.

=COUNTIF($C$2:$C$8,C2)>1 Explain

TRUE for Ben and Gus, who both sold 48,500.

Common mistakes

  • Comparison without quotes. =COUNTIF(C2:C8,>50000) is an error. Write ">50000" in quotes, or ">"&F2 to use a cell.
  • Putting the cell inside the quotes. ">F2" compares with the text "F2". Join the cell instead: ">"&F2.
  • Long numbers. COUNTIF compares numbers, so IDs longer than 15 digits (like card numbers) that differ only at the end are counted as equal.

Notes

  • "" counts empty cells (and formulas returning ""); "<>" counts non-empty ones.
  • To match a real * or ?, put a tilde in front: "~*".
  • Related functions: COUNTIFS, SUMIF, AVERAGEIF, COUNTA.
Kyle Stott

Author

Kyle Stott

Certified Microsoft Excel Expert

Kyle has worked professionally with Microsoft Excel for over a decade and has consulted on and taught Excel best practices to over 400 companies across 30+ countries.