How to Use COUNTIF in Excel: Simple Guide with Examples

Use the COUNTIF function in Excel to count the number of cells in a range that meet a specific condition. The syntax is COUNTIF(range, criteria), where range is the cells to check and criteria is the condition to match.

Syntax

The COUNTIF function has two parts:

• range: The group of cells you want to check.
• criteria: The condition that tells Excel which cells to count.

The criteria can be a number, text, expression, or even a cell reference.

excel

COUNTIF(range, criteria)

Example

This example counts how many cells in A1:A6 contain the number 10.

excel

A1: 10
A2: 15
A3: 10
A4: 20
A5: 10
A6: 5

=COUNTIF(A1:A6, 10)

Output

3

Common Pitfalls

Common mistakes include:

• Not using quotes around text criteria (e.g., use "apple" not apple).
• Using wrong operators without quotes (e.g., ">10" instead of just >10).
• For partial matches, forgetting to use wildcards like *.

excel

Wrong: =COUNTIF(A1:A6, apple)
Right: =COUNTIF(A1:A6, "apple")

Wrong: =COUNTIF(A1:A6, >10)
Right: =COUNTIF(A1:A6, ">10")

Quick Reference

Criteria Type	Example	Description
Number	10	Counts cells equal to 10
Text	"apple"	Counts cells containing the word apple
Expression	">5"	Counts cells greater than 5
Wildcard	"a*"	Counts cells starting with letter a
Cell Reference	B1	Counts cells equal to value in B1

Key Takeaways

COUNTIF counts cells in a range that meet a specific condition.

Always put text criteria and expressions in quotes.

Use wildcards like * for partial text matches.

Criteria can be numbers, text, expressions, or cell references.

COUNTIF is great for quick counts based on simple rules.