A comparison without quotes
=COUNTIF(C2:C8,>100)
The checker says: Put the condition in double quotes: ">100".
The sign and the number go inside the quotes together.
Excel function
COUNTIF in Excel
COUNTIF counts the cells that meet a condition: how many expenses are Travel, or how many are over 100.
Syntax
=COUNTIF(range, criteria)
| Argument | What it does |
|---|---|
| range | The cells to check and count. |
| criteria | The condition: text or a comparison in quotes, like "Travel" or ">100", or a cell such as F2. |
Here’s what that formula is doing.
=COUNTIF(B2:B8,"Travel") checks the seven categories in B2:B8 and counts the three that say Travel.
Try it with real data.
Type a formula, then check it. Hints step in when you’re stuck, and the last one shows the answer.
Example data
Task 1
In B10, count how many expenses are Travel.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Category | Amount | Paid |
| 2 | Taxi to airport | Travel | 42.50 | Yes |
| 3 | Team lunch | Meals | 86.00 | No |
| 4 | Hotel, 2 nights | Travel | 310.00 | Yes |
| 5 | Printer ink | Office | 38.90 | Yes |
| 6 | Client dinner | Meals | 124.00 | Yes |
| 7 | Train ticket | Travel | 27.00 | No |
| 8 | Notebooks | Office | 15.60 | No |
| 9 | ||||
| 10 | Travel expenses | (answer cell) | ||
| 11 | Over 100 | |||
| 12 | Unpaid |
Scroll the sheet sideways to see every column.
Nice. Try the next task
Example data
Task 2
In B11, count the expenses over 100.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Item | Category | Amount | Paid |
| 2 | Taxi to airport | Travel | 42.50 | Yes |
| 3 | Team lunch | Meals | 86.00 | No |
| 4 | Hotel, 2 nights | Travel | 310.00 | Yes |
| 5 | Printer ink | Office | 38.90 | Yes |
| 6 | Client dinner | Meals | 124.00 | Yes |
| 7 | Train ticket | Travel | 27.00 | No |
| 8 | Notebooks | Office | 15.60 | No |
| 9 | ||||
| 10 | Travel expenses | 3 | ||
| 11 | Over 100 | (answer cell) | ||
| 12 | Unpaid |
Scroll the sheet sideways to see every column.
That’s both. Practise the whole course free.
Common mistakes, and what our checker says.
Each message below is the checker’s real reply to that formula on the example data.
The sign split from the number
=COUNTIF(C2:C8,">"100)
The checker says: Put the sign and the number inside the same quotes: ">100".
To compare with a value in a cell instead, join them with &: ">"&E1.
More than, or at least?
=COUNTIF(C2:C8,">=100")
The checker says: Check the boundary. >= counts the value itself too. Use > to leave it out.
">100" means more than 100. ">=100" counts 100 itself too.
COUNT instead of COUNTIF
=COUNT(B2:B8)
The checker says: This exercise practises COUNTIF. Use COUNTIF in your formula.
COUNT only counts cells that hold numbers. The categories are text, so it returns 0.
Questions
Can COUNTIF use wildcards?
Yes. * stands for any run of characters and ? for one character, so "*lunch*" counts any cell that contains lunch.
How do I count with two conditions?
Use COUNTIFS: =COUNTIFS(B2:B8,"Travel",D2:D8,"No") counts the Travel expenses that aren’t paid yet.
Is COUNTIF case-sensitive?
No. It treats "travel" and "Travel" the same.
COUNTIF is part of module 7 of Excel Foundations: Count and add with conditions.
Start practising now.
Excel Foundations is free, with no sign-up. Your progress is saved in this browser.