Practice by topic / Excel / Counting & Averaging
Counting and averaging are how you summarise a list: how many orders, how many late, what the typical value is. Most reports start here. These 10 exercises build up step by step, starting with count orders from a region above a value, count employees in a department with high ratings, count products in stock and on sale.
3 of 10 free · no sign-up needed · graded on the formula you write
A1:A4 hold regions, B1:B4 hold order amounts. Write a formula in C1 that counts orders from "West" that are also over 100.
| A | B | C | |
|---|---|---|---|
| 1 | West | 120 | ? |
| 2 | East | 200 | |
| 3 | West | 80 | |
| 4 | West | 150 |
Pass both range/criteria pairs: =COUNTIFS(A1:A4,"West",B1:B4,">100")
Right — COUNTIFS requires every criteria pair to match on the same row, giving you a combined condition.
Expected result in C1: 2
A1:A4 hold departments, B1:B4 hold performance ratings. Write a formula in C1 that counts "Sales" employees rated 4 or higher.
| A | B | C | |
|---|---|---|---|
| 1 | Sales | 5 | ? |
| 2 | Sales | 3 | |
| 3 | Support | 4 | |
| 4 | Sales | 4 |
Use =COUNTIFS(A1:A4,"Sales",B1:B4,">=4")
Good — the two conditions must both be true on the same row to count.
Expected result in C1: 2
A1:A4 hold stock counts, B1:B4 hold sale flags ("yes"/"no"). Write a formula in C1 that counts products with stock over 0 that are on sale.
| A | B | C | |
|---|---|---|---|
| 1 | 5 | yes | ? |
| 2 | 0 | yes | |
| 3 | 10 | no | |
| 4 | 3 | yes |
Use =COUNTIFS(A1:A4,">0",B1:B4,"yes")
Right — combining a numeric and a text criteria across two ranges works the same way.
Expected result in C1: 2
A1:A4 hold class names, B1:B4 hold scores. Write a formula in C1 that counts "Math" scores that are 60 or above.
| A | B | C | |
|---|---|---|---|
| 1 | Math | 75 | ? |
| 2 | Science | 55 | |
| 3 | Math | 40 | |
| 4 | Math | 88 |
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 →A1:A4 hold transaction types, B1:B4 hold amounts. Write a formula in C1 that counts "refund" transactions under 50.
| A | B | C | |
|---|---|---|---|
| 1 | refund | 30 | ? |
| 2 | sale | 100 | |
| 3 | refund | 80 | |
| 4 | refund | 15 |
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 →A1:A4 hold priorities, B1:B4 hold statuses. Write a formula in C1 that counts "high" priority tickets that are "open".
| A | B | C | |
|---|---|---|---|
| 1 | high | open | ? |
| 2 | low | open | |
| 3 | high | closed | |
| 4 | high | 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 →A1:A4 hold categories, B1:B4 hold prices. Write a formula in C1 that counts "Electronics" items priced under 100.
| A | B | C | |
|---|---|---|---|
| 1 | Electronics | 80 | ? |
| 2 | Electronics | 150 | |
| 3 | Books | 20 | |
| 4 | Electronics | 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 →A1:A4 hold years of experience, B1:B4 hold interview scores. Write a formula in C1 that counts applicants with 3+ years experience and a score above 80.
| A | B | C | |
|---|---|---|---|
| 1 | 5 | 85 | ? |
| 2 | 2 | 90 | |
| 3 | 4 | 75 | |
| 4 | 6 | 88 |
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 →A1:A4 hold carrier names, B1:B4 hold days late (0 = on time). Write a formula in C1 that counts "FastShip" shipments that were late (days late > 0).
| A | B | C | |
|---|---|---|---|
| 1 | FastShip | 2 | ? |
| 2 | FastShip | 0 | |
| 3 | SlowCo | 3 | |
| 4 | FastShip | 1 |
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 →A1:A4 hold ages, B1:B4 hold incomes. Write a formula in C1 that counts people aged 30 or older earning over 50000.
| A | B | C | |
|---|---|---|---|
| 1 | 35 | 60000 | ? |
| 2 | 25 | 55000 | |
| 3 | 40 | 45000 | |
| 4 | 32 | 70000 |
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 →