Learning Excel: COUNTIF
Counting what matches
Yasmina puts the week's sales slips on a pile. "How many pieces of clothing went out of the door?" Counting with a condition: that's what COUNTIF is for.
COUNTIF
COUNTIF counts the cells in a range that meet a condition. The first argument is the range, the second the condition: the criterion.
I2 holds =COUNTIF(B2:B16,"Clothing"): Clothing appears six times in the Category column. Upper or lower case doesn't matter.
I2
=COUNTIF(B2:B16,"Clothing")
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | |||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | 6 | ||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | ||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | ||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | ||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | ||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | ||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | ||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | ||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | ||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | ||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | ||||
| 13 | Vase | Household | £7.00 | Sat | Grace | ||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | ||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | ||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | ||||
| 17 |
Try it yourself
How many sales did Omar make? Count them in I2.
| A | B | C | D | E | F | G | H | I | J | |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Item | Category | Price | Day | Volunteer | Limit | ||||
| 2 | Dress green | Clothing | £12.00 | Mon | Sophie | £10.00 | ||||
| 3 | Book fiction | Books | £2.00 | Mon | Omar | |||||
| 4 | Lamp | Household | £8.00 | Tue | Sophie | |||||
| 5 | T-shirt S | Clothing | £3.00 | Tue | Grace | |||||
| 6 | Dressing gown | Clothing | £6.00 | Wed | Omar | |||||
| 7 | Puzzle | Toys | £4.00 | Wed | Grace | |||||
| 8 | Book cooking | Books | £3.00 | Thu | Sophie | |||||
| 9 | Radio | Electrical | £15.00 | Thu | Omar | |||||
| 10 | T-shirt XL | Clothing | £4.00 | Fri | Sophie | |||||
| 11 | Dress black | Clothing | £18.00 | Fri | Grace | |||||
| 12 | What? Game | Toys | £5.00 | Sat | Omar | |||||
| 13 | Vase | Household | £7.00 | Sat | Grace | |||||
| 14 | T-shirt M | Clothing | £3.00 | Sat | Sophie | |||||
| 15 | Bookend | Household | £9.00 | Sat | Omar | |||||
| 16 | Kids' bike | Toys | £35.00 | Sat | Grace | |||||
| 17 |
I2
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson