Practice by topic / Excel / Counting & Averaging
COUNTIF criteria do more than exact matches: "*blue*" counts cells that contain a word, "SG-*" cells that start with it, ">100" numbers above a limit, "<>Closed" everything except one value and "<>" every filled cell. Joining an operator to a cell, as in ">="&D1, keeps the limit editable. These 10 exercises build up step by step, starting with count names that contain a word, count codes that start with a prefix, count orders over 100.
3 of 10 free · no sign-up needed · graded on the formula you write
A2:A7 hold product names. Write a formula in C1 that counts how many contain the word "blue" anywhere in the name.
| A | B | C | |
|---|---|---|---|
| 1 | Product | ? | |
| 2 | Blue mug | ||
| 3 | Red mug | ||
| 4 | Blue plate | ||
| 5 | Green mug | ||
| 6 | Blue bowl | ||
| 7 | Light blue cup |
Use =COUNTIF(A2:A7,"*blue*")
Right — asterisks on both sides mean 'anything before, anything after', and COUNTIF ignores capital letters.
Expected result in C1: 4
A2:A6 hold order codes. Write a formula in C1 that counts the codes that start with "SG-".
| A | B | C | |
|---|---|---|---|
| 1 | Order code | ? | |
| 2 | SG-101 | ||
| 3 | MY-204 | ||
| 4 | SG-330 | ||
| 5 | TH-412 | ||
| 6 | ID-118 |
Use =COUNTIF(A2:A6,"SG-*")
Good — an asterisk only at the end means 'starts with'.
Expected result in C1: 2
A2:A6 hold order values. Write a formula in C1 that counts orders greater than 100.
| A | B | C | |
|---|---|---|---|
| 1 | Order value | ? | |
| 2 | 120 | ||
| 3 | 85 | ||
| 4 | 240 | ||
| 5 | 60 | ||
| 6 | 310 |
Use =COUNTIF(A2:A6,">100")
Right — comparison operators go inside the quotes with the number.
Expected result in C1: 3
A2:A6 hold scores and D1 holds the pass mark. Write a formula in D2 that counts scores at or above the pass mark, so it updates if D1 changes.
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Score | Pass mark | 75 | |
| 2 | 72 | ? | ||
| 3 | 45 | |||
| 4 | 88 | |||
| 5 | 91 | |||
| 6 | 60 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold ticket statuses. Write a formula in C1 that counts the tickets whose status is not "Closed".
| A | B | C | |
|---|---|---|---|
| 1 | Status | ? | |
| 2 | Open | ||
| 3 | Closed | ||
| 4 | Pending | ||
| 5 | Closed | ||
| 6 | Open |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 is a sign-up list with a blank row. Write a formula in C1 that uses COUNTIF to count the cells that are not blank.
| A | B | C | |
|---|---|---|---|
| 1 | Sign-ups | ? | |
| 2 | Ali | ||
| 3 | |||
| 4 | Mei | ||
| 5 | Raj | ||
| 6 | Siti |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 record payment status, and a blank cell means unpaid. Write a formula in C1 that counts the blank cells.
| A | B | C | |
|---|---|---|---|
| 1 | Payment | ? | |
| 2 | Paid | ||
| 3 | Paid | ||
| 4 | |||
| 5 | Paid |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold product codes. Write a formula in C1 that counts the codes that are exactly 5 characters long, using the ? wildcard.
| A | B | C | |
|---|---|---|---|
| 1 | Code | ? | |
| 2 | AB123 | ||
| 3 | AB12 | ||
| 4 | CD456 | ||
| 5 | EF7890 | ||
| 6 | GH001 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold survey answers typed in different capitalisations. Write a formula in C1 that counts every "yes" answer.
| A | B | C | |
|---|---|---|---|
| 1 | Answer | ? | |
| 2 | yes | ||
| 3 | Yes | ||
| 4 | NO | ||
| 5 | YES | ||
| 6 | no |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →A2:A6 hold ages. Write a formula in C1 that counts ages from 25 to 45 inclusive.
| A | B | C | |
|---|---|---|---|
| 1 | Age | ? | |
| 2 | 15 | ||
| 3 | 42 | ||
| 4 | 28 | ||
| 5 | 51 | ||
| 6 | 47 |
Solution and instant grading: open it in the practice app (use one of your 20 free picks, or Pro).
Try it in the live spreadsheet →