COUNTIF in Excel: Count Cells That Match a Condition

COUNTIF counts how many cells in a range meet one condition. How many orders are "Paid"? How many months beat target? How many students scored below 50? One formula each.

Syntax

=COUNTIF(range, criteria)

Text matches

=COUNTIF(B2:B4,"Paid")   → 2

COUNTIF ignores case: "paid" and "PAID" match too.

Numbers with operators

Put the operator and number together in quotes:

=COUNTIF(C2:C50,">5000")
=COUNTIF(C2:C50,"<=0")
=COUNTIF(C2:C50,"<>0")

Criteria from a cell

To make the threshold editable, join the operator to a cell with &:

=COUNTIF(C2:C50,">"&F1)

This is a very common job-test task, and the & is the part people forget.

Wildcards for partial matches

• * = any number of characters
• ? = exactly one character

=COUNTIF(A2:A100,"*coffee*")   contains "coffee"
=COUNTIF(A2:A100,"A*")         starts with A
=COUNTIF(A2:A100,"???")        exactly 3 characters

Blanks and non-blanks

=COUNTIF(B2:B100,"")    blank
=COUNTIF(B2:B100,"<>")  not blank

Percentage of matches

=COUNTIF(B2:B100,"Paid")/COUNTA(B2:B100)

Format as a percentage.

Find duplicates

Next to a list, =COUNTIF($A$2:$A$100,A2)>1 returns TRUE for every value that appears more than once. Note the $ on the range so it doesn't shift when filled down. See remove duplicates.

Several conditions

COUNTIF handles one condition. For two or more (Paid and over 100), use COUNTIFS.

Common mistakes

• Missing quotes: =COUNTIF(C2:C50,>5000) is an error.
• Operator inside the cell reference: ">F1" counts cells greater than the text "F1". Use ">"&F1.
• Extra spaces in data stop exact matches. Clean with TRIM.
• Range not locked when filling down.

Practise it

Count and sum by condition (COUNTIF, SUMIFS) is a coming unit in Cellio. Our practice exercises include COUNTIF questions with answers in the meantime.

Frequently asked questions

How do I count cells with specific text in Excel?

=COUNTIF(range,"text"), or =COUNTIF(range,"*text*") to match text anywhere in the cell.

How do I count cells greater than a value?

=COUNTIF(range,">100"), or =COUNTIF(range,">"&F1) to use a value in another cell.

Is COUNTIF case-sensitive?

No. For case-sensitive counts, use SUMPRODUCT with EXACT.

What is the difference between COUNTIF and COUNTIFS?

COUNTIF takes one condition; COUNTIFS takes several, all of which must be true.