COUNTIF in Excel
Last updated
COUNTIF counts the cells in a range that meet one condition. For example, =COUNTIF(C2:C9,"Delivered") counts how many orders in C2:C9 have the status Delivered. The condition can be text, a comparison like ">500", or a cell reference.
COUNTIF syntax
=COUNTIF(range, criteria)
- range
- The cells to check, e.g.
C2:C9. Lock it with$($C$2:$C$9) before copying the formula. - criteria
- The condition a cell must meet to be counted: a text in quotes (
"Delivered"), a comparison in quotes (">500","<>") or a cell reference (F2). It is not case-sensitive.
Practice COUNTIF here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Count orders with a given status
In F2, use COUNTIF to count how many orders in C2:C9 have the status Delivered.
| Order no. | Sales rep | Status | Amount ($) | Delivered orders | |
| C-2401 | Olivia | Delivered | 320.00 | ||
| C-2402 | James | Pending | 780.00 | ||
| C-2403 | Olivia | Delivered | 145.00 | ||
| C-2404 | Sofia | Cancelled | 1,250.00 | ||
| C-2405 | James | Delivered | 560.00 | ||
| C-2406 | Olivia | Pending | 90.00 | ||
| C-2407 | Sofia | Delivered | 430.00 | ||
| C-2408 | James | Cancelled | 615.00 |
How to use COUNTIF, step by step
- Click the cell where you want the count and type
=COUNTIF(. - Select the range to check, then type a comma. Press F4 to lock it with $ if you will copy the formula.
- Type the condition: a text or comparison in double quotes, or click the cell that holds it.
- Close the parenthesis and press Enter.
COUNTIF examples
Count a text
=COUNTIF(C2:C9,"Delivered")
Counts the cells of C2:C9 that contain exactly Delivered, whatever the capitalisation.
Count values above a number
=COUNTIF(D2:D9,">50")
Counts the values greater than 50. Use ">=50" to include 50 itself, "<50" for values below it.
Take the condition from a cell
=COUNTIF($B$2:$B$9,F2)
Counts the cells equal to F2. To combine a comparison with a cell, join them with &: =COUNTIF(D2:D9,">"&F2).
Count non-empty cells
=COUNTIF(A2:A100,"<>")
"<>" means “not equal to nothing”, so it counts every cell that isn’t blank.
Common COUNTIF problems and how to fix them
| Result | Cause | Fix |
|---|---|---|
| 0 instead of a count | The criterion doesn’t match the cells exactly: a typo, extra spaces in the data, or numbers stored as text. | Check the spelling, clean the data with TRIM, and convert text to numbers. |
| Count too low after copying | The range wasn’t locked, so it slid down row by row when you copied the formula. | Lock the range with $ ($C$2:$C$9) before copying. |
| #NAME? | A text criterion without quotes, e.g. =COUNTIF(C2:C9,Delivered). | Put text criteria in double quotes: "Delivered". |
| #VALUE! | The range points to another workbook that is closed. | Open the other workbook, or copy the data into your file. |
COUNTIF vs COUNTIFS, COUNT and COUNTA
COUNTIF takes one condition. COUNTIFS takes several range and condition pairs and counts only the rows that meet all of them, e.g. orders that are Delivered and over $500. COUNT counts cells that contain numbers, and COUNTA counts every cell that isn’t empty, with no condition at all.
Frequently asked questions
Why does my COUNTIF return 0?
Nothing in the range matches the criterion exactly. Look for typos, extra spaces in the cells, or numbers stored as text. Also check that the range covers the right column.
How do I count cells that contain a word?
Use the * wildcard around the word: =COUNTIF(A2:A100,"*invoice*") counts every cell that contains invoice anywhere in its text.
How do I find duplicates with COUNTIF?
Next to your list, enter =COUNTIF($A$2:$A$100,A2) and copy it down. Any result above 1 is a value that appears more than once.
Is COUNTIF case-sensitive?
No. "delivered", "Delivered" and "DELIVERED" are counted the same way.
Keep practising
All 10 COUNTIF exercises, with explanations and progress tracking.
Open the COUNTIF practice set