How to Use COUNTIF in Excel (With Examples)

How to Use the COUNTIF Function in Excel (With Examples)

By Rinkesh Shah · Business Analyst, 8 years in data · Updated 2026-10-30

▶ Video coming soon

COUNTIF counts the cells that meet one condition.

Syntax

=COUNTIF(range, criteria)

Common examples

Count cells equal to Pending: =COUNTIF(B2:B100, "Pending")
Count numbers above 50: =COUNTIF(C2:C100, ">50")
Count cells that contain "tax": =COUNTIF(A2:A100, "*tax*")
Count blank cells: =COUNTIF(A2:A100, "")
Count non-blank cells: =COUNTIF(A2:A100, "<>")

Use a cell as the condition

=COUNTIF(B2:B100, F1) counts whatever is typed in F1.

Count duplicates

Put =COUNTIF($A$2:$A$100, A2) next to each row. Any result above 1 means the value is repeated.

Count unique values

In modern Excel use =COUNTA(UNIQUE(A2:A100)).

Common mistakes

Forgetting quotes around operators: write ">50", not >50.
Extra spaces in the data, so "Pending " does not match "Pending".
COUNTIF is not case sensitive.

For two or more conditions, use COUNTIFS.

Frequently asked questions

What does COUNTIF do in Excel?

COUNTIF counts the cells in a range that meet one condition, such as text, a number or a comparison like ">50".

How do I count cells that contain specific text?

Use =COUNTIF(range,"text") for an exact match or =COUNTIF(range,"*text*") to count cells that contain the text anywhere.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF uses one condition. COUNTIFS counts rows that meet several conditions at the same time.

Practice this online, free

Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.

Practice these formulas: COUNTIF, COUNTIFS, COUNTA.

Start Excel practice → Case studies