Excel COUNT Practice Question: Some bill amounts were entered as text (n/a). Using SUM and COUNT, find the…
The question
Some bill amounts were entered as text (n/a). Using SUM and COUNT, find the average value of the valid bills and put it in B8.
The data
| A | B | |
|---|---|---|
| 1 | Bill | Amount |
| 2 | B1 | 500 |
| 3 | B2 | n/a |
| 4 | B3 | 700 |
| 5 | B4 | 900 |
| 6 | B5 | NA |
| 7 | B6 | 400 |
| 8 | Avg valid | ? |
The yellow cell is where your answer goes.
Show answer
Answer in B8: 625
How it works
Explanation: SUM ignores text and COUNT counts only numbers, so dividing one by the other averages just the valid bills.
Syntax: =SUM(B2:B7)/COUNT(B2:B7)
Valid bills: 500, 700, 900, 400. Sum = 2500. Count = 4. 2500 / 4 = 625.
Try it yourself in the live sheet
Type your formula in the highlighted cell and get instant feedback. Free, no Excel install needed.
Try this question in the live sheet →COUNT syntax
=COUNT(value1, [value2], ...)
Counts how many cells in a range contain numbers. COUNT is used in data analysis work to summarize or test patterns across a data set. Full COUNT guide.
More COUNT practice questions
- Some cells in the Marks column hold text. Using COUNT, count how many cells hold actual…
- Using COUNT, find how many invoices have an amount entered and put it in B7.
- Two days have no units recorded. Using COUNT, find how many days have a number entered…
- Absent students are marked AB. Using COUNT, find how many students appeared and put it…
- Using one COUNT over both columns, find the total number of readings taken (morning and…
- A blank Delivery days cell means the order is still pending. Using COUNT and COUNTA,…
- A zero reading is still a reading. Using COUNT, find how many readings were recorded…
- New customers get the next serial number. Using COUNT, find the next serial number and…
All practice questions · Excel exercises · Interview prep · Cheat sheet