The COUNTIF function in Excel counts the number of cells in a range that meet a specific condition. It is one of the most useful functions for analyzing lists, tracking categories, and summarizing data.
Instead of manually counting entries that match certain criteria, COUNTIF lets Excel do the work instantly, even across hundreds or thousands of rows.
Whether you’re counting sales by region, tracking completed tasks, or identifying how many times a value appears, COUNTIF is a must-know function for everyday Excel use.
In this guide, you’ll learn:
- What the COUNTIF function does
- How to write a COUNTIF formula
- How to count cells that match text
- How to count cells using numbers or conditions
- Common mistakes to avoid when using COUNTIF
- Real-world ways COUNTIF is used in Excel
What is the COUNTIF Function?
The COUNTIF function counts the number of cells within a range that meet a specified condition (called a criteria).
COUNTIF Syntax
=COUNTIF(range, criteria)Argument Definitions:
- range — The group of cells you want to evaluate
- criteria — The condition that determines which cells are counted
The criteria can include:
- Text (like “West”)
- Numbers (like 50)
- Expressions (like “>=100”)
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> COUNTIF Function Data
Counting Text Values
Let’s start with a simple example that counts how many times a specific word appears.
Enter the COUNTIF Formula
In cell A9 type:
=COUNTIF(A2:A7,"Adams")Press Enter.
Excel will return 3
This means Adams appears 3 times in the list.
Counting Numbers Greater Than a Value
COUNTIF also works with numeric conditions.
In cell C9 enter this formula:
=COUNTIF(C2:C7,">100")Press Enter.
Excel returns 3
This counts:
- 120
- 200
- 150
Using Cell References as Criteria
Instead of typing the criteria directly, you can reference a cell.
Use COUNTIF with a Cell Reference
In cell E9 enter:
=COUNTIF(E2:E7,G1)Press Enter.
Excel returns 3
Now you can change G1 to another name and the result updates automatically.
Understanding COUNTIF Criteria
Criteria can be written in several ways depending on what you’re counting.
Text Criteria
=COUNTIF(A2:A7,"Adams")Counts cells that contain:
AdamsLogical Criteria Examples
=COUNTIF(A2:A7,"<50")
=COUNTIF(A2:A7,">=75")
=COUNTIF(A2:A7,"<>100")These allow Excel to count based on rules.
Common COUNTIF Mistakes to Avoid
Even though COUNTIF is simple, a few common mistakes can cause incorrect results.
Mistake 1 — Forgetting Quotes Around Conditions
Incorrect:
=COUNTIF(A2:A7,>100)Correct:
=COUNTIF(A2:A7,">100")Always use quotes around comparison operators.
Mistake 2 — Counting Text with Extra Spaces
Text must match exactly.
Example:
"Adams "(with a space)
is different from:
"Adams"Use TRIM if needed to remove extra spaces.
Mistake 3 — Using the Wrong Range
Make sure the selected range contains the correct data.
Incorrect ranges produce incorrect counts.
Real-World Uses for COUNTIF
COUNTIF appears everywhere in real-world spreadsheets.
Here are practical examples:
- Counting how many sales were made in a specific region
- Counting completed vs incomplete tasks
- Counting how many times a product was sold
- Tracking attendance records
- Counting how many values exceed a target
Once you learn COUNTIF, you’ll start seeing opportunities to use it everywhere.
COUNTIF vs COUNT — What’s the Difference?
These two functions are often confused.
COUNT Function
Counts:
- Numbers only
Example:
=COUNT(A2:A7)COUNTIF Function
Counts:
- Cells matching a condition
Example:
=COUNTIF(A2:A7,"Adams")If you need conditions, COUNTIF is the correct choice.
Example Dataset Used in This Tutorial
You can download the sample data we’ll be using in this tutorial by clicking on this link—> COUNTIF Function Data
