On this page ⌄
COUNTIF answers "how many?" questions. How many orders came from the North? How many students scored over 80? How many invoices are still blank? It counts the cells that match a condition, and it's one of the first functions worth learning after SUM.
The examples use this sales log in A1:F11:
| A: Date | B: Region | C: Rep | D: Product | E: Units | F: Revenue | |
|---|---|---|---|---|---|---|
| 2 | Jan 4, 2026 | North | Ava | Laptop | 3 | $3,600 |
| 3 | Jan 9, 2026 | South | Leo | Monitor | 5 | $1,250 |
| 4 | Jan 15, 2026 | North | Maya | Laptop | 2 | $2,400 |
| 5 | Jan 22, 2026 | East | Sam | Keyboard | 12 | $720 |
| 6 | Feb 3, 2026 | West | Zoe | Monitor | 4 | $1,000 |
| 7 | Feb 11, 2026 | North | Ava | Keyboard | 8 | $480 |
| 8 | Feb 19, 2026 | South | Ivy | Laptop | 4 | $4,800 |
| 9 | Feb 27, 2026 | East | Sam | Monitor | 6 | $1,500 |
| 10 | Mar 6, 2026 | West | Noah | Laptop | 1 | $1,200 |
| 11 | Mar 14, 2026 | North | Maya | Monitor | 7 | $1,750 |
The COUNTIF syntax
COUNTIF has just two arguments:
| Argument | What it means | Example |
|---|---|---|
| range | The cells to look through | B2:B11 |
| criteria | The condition a cell must meet to be counted | "North" |
How many orders came from the North region?
=COUNTIF(B2:B11, "North")→ 4COUNTIF checks each cell in B2:B11 and counts the ones that say North. The match isn't case-sensitive, so "north" and "NORTH" count too.
Now you try. Click the column you want to count, then finish the formula.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
How many orders were for Laptops?
Write your formula in the green cell, I2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
Counting numbers above or below a value
Put the operator and the number inside quotes. How many orders had more than 5 units?
=COUNTIF(E2:E11, ">5")→ 4And how many orders brought in at least $2,000?
=COUNTIF(F2:F11, ">=2000")→ 3| You want to count cells that are | Criteria |
|---|---|
| Equal to some text | "North" |
| Equal to a number | 5 |
| Greater than 5 | ">5" |
| Less than or equal to 5 | "<=5" |
| Not equal to North | "<>North" |
| Equal to the value in H2 | H2 |
| Greater than the value in H2 | ">"&H2 |
| Blank | "" |
| Not blank | "<>" |
Using a cell as the condition
Typing "North" into the formula works, but it's more useful to point at a cell. Then you can change the cell and the count updates. If H2 contains a rep's name:
=COUNTIF(C2:C11, H2)To combine an operator with a cell, join them with &. If H3 holds a target number, this counts orders above it:
=COUNTIF(F2:F11, ">"&H3)Partial matches with wildcards
An asterisk * stands for any number of characters, and a question mark ? stands for exactly one. Count the products that end in "board":
=COUNTIF(D2:D11, "*board")→ 2And the ones that start with M:
=COUNTIF(D2:D11, "M*")→ 4Finding duplicates with COUNTIF
COUNTIF is a quick way to spot values that appear more than once. Next to each rep, count how many times that rep appears in the whole column:
=COUNTIF($C$2:$C$11, C2)→ 2Ava appears twice. Anything above 1 is a repeat. The dollar signs keep the range fixed as you copy the formula down. You can use the same formula as a conditional formatting rule, with >1 on the end, to highlight duplicates automatically.
COUNTIFS: more than one condition
COUNTIFS counts rows where every condition is true. Write the range and its condition as pairs. How many laptop orders came from the North?
=COUNTIFS(B2:B11, "North", D2:D11, "Laptop")→ 2To count values between two numbers, use the same range twice. Orders between $1,000 and $2,000:
=COUNTIFS(F2:F11, ">=1000", F2:F11, "<=2000")→ 5And orders placed in January 2026:
=COUNTIFS(A2:A11, ">="&DATE(2026,1,1), A2:A11, "<"&DATE(2026,2,1))→ 4Turning a count into a percentage
Divide by the total number of rows to see what share of orders match. COUNTA counts every non-empty cell:
=COUNTIF(B2:B11, "North") / COUNTA(B2:B11)→ 40%Format the cell as a percentage to see 40% instead of 0.4.
COUNT, COUNTA, COUNTBLANK and COUNTIF
| Function | Counts |
|---|---|
| COUNT | Cells that contain numbers |
| COUNTA | Cells that aren't empty |
| COUNTBLANK | Empty cells |
| COUNTIF | Cells that match one condition |
| COUNTIFS | Cells that match every condition |
Common COUNTIF mistakes
| Problem | What's going on | Fix |
|---|---|---|
| Count is 0 | Extra spaces, or numbers stored as text | Clean the data with TRIM, or check the cell format |
">"H2 gives an error | The operator and cell weren't joined | Write ">"&H2 |
#VALUE! in COUNTIFS | The ranges are different sizes | Make every range the same height |
| Expecting case to matter | COUNTIF ignores upper and lower case | Use SUMPRODUCT with EXACT for case-sensitive counts |
Key takeaways
- COUNTIF counts cells that meet one condition. COUNTIFS needs every condition to be true.
- Put operators in quotes, and join them to cells or dates with
&. - Use
*and?for partial matches. - To add up values instead of counting rows, use SUMIF and SUMIFS.
Practice
Keep going with COUNTIF and COUNTIFS exercises
Reading gets you started. Solving a few of these is what makes it stick.
- 1Count one productFreeHow many orders were for Laptops?→
- 2Orders over $1,000 PremiumHow many orders had revenue over $1,000?→
- 3Two conditions at once PremiumWhat was the revenue from Monitors sold in the South region?→
About the author
Brian Millot
Brian Millot is an indie hacker based in France who builds small, focused web products. He built and runs Excel Formula Practice, from the in-browser spreadsheet that checks your formulas to the exercise library, and he writes the formula guides.