How to Use COUNTIF in Excel
Counts how many cells match a condition — duplicates, blanks, specific text, or values above a limit.
What is COUNTIF in Microsoft Excel?
COUNTIF returns a number: how many cells in a range meet your criteria. It handles text, numbers, wildcards and comparison operators. It is the go-to function for duplicate checks, attendance counts and quick data-quality audits.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| range | Required | The cells to inspect. Text, numbers and blanks are all valid targets. |
| criteria | Required | What to count. A value, a cell reference, a wildcard pattern or an operator string such as ">=100". |
When to Use COUNTIF (Real-World Business Scenarios)
- 1
Counting how many orders are still Pending or how many employees are in one department.
- 2
Finding duplicates with =COUNTIF(A:A,A2)>1 before you remove them.
- 3
Counting blanks with =COUNTIF(A2:A100,"") or non-blanks with "<>".
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Order ID | Status |
|---|---|
| ORD-101 | Delivered |
| ORD-102 | Pending |
| ORD-103 | Delivered |
How it works: Scans the Status column and counts every cell that contains exactly 'Delivered'. The result is 2 because ORD-101 and ORD-103 are delivered.
Common Errors & How to Fix Them
Why it happens: The cell contains extra spaces, or the match is case-insensitive but the value has a non-breaking space from a web export.
Fix: Clean the source with TRIM and CLEAN, or use a wildcard: =COUNTIF(B:B,"*Delivered*").
Why it happens: The numbers are stored as text, so COUNTIF treats "100" and 100 differently in some imports.
Fix: Convert the column to numbers first, or count with SUMPRODUCT(--(range=100)) for full control.
Try COUNTIF Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (COUNTIF)
What does the COUNTIF formula do in Excel?
COUNTIF returns a number: how many cells in a range meet your criteria. It handles text, numbers, wildcards and comparison operators. It is the go-to function for duplicate checks, attendance counts and quick data-quality audits.
What is the syntax for COUNTIF?
What are common mistakes when using COUNTIF?
Counts 0 for text that clearly exists: The cell contains extra spaces, or the match is case-insensitive but the value has a non-breaking space from a web export. (Clean the source with TRIM and CLEAN, or use a wildcard: =COUNTIF(B:B,"*Delivered*").) Numbers are not counted: The numbers are stored as text, so COUNTIF treats "100" and 100 differently in some imports. (Convert the column to numbers first, or count with SUMPRODUCT(--(range=100)) for full control.)
Related Excel Formulas
Keep building your formula toolkit with these math & stats functions.
- SUMIFSSums numerical values across multiple conditions (e.g. Sales in North Region for Q1).
- COUNTIFSCounts the number of rows that satisfy multiple simultaneous criteria.
- SUMAdds numbers across cells, ranges and entire columns — the first formula most people ever type in Excel.
- SUMIFAdds only the numbers that match one condition — the fastest way to total a region, product or status without a Pivot Table.
- AVERAGEReturns the arithmetic mean of a range while automatically ignoring blanks and text.
- MAXReturns the largest number in a range — highest sale, top score, latest date or most recent invoice.
Master COUNTIF in Basic Excel
Learn COUNTIF along with our formula library, practice files, and verifiable certificates in our complete curriculum.