Excel SUM, AVERAGE and COUNT: Summarise Numbers Correctly
On this page
A summary can look convincing while using the wrong cells or the wrong denominator. Excel SUM, AVERAGE and COUNT are easy to start using, but understanding blanks, zeros and text is what makes your results trustworthy.
Before you start: Use a new practice workbook. The names and figures below are fictional. Enter the headers in row 1 and the first record in row 2 unless a step says otherwise. Formulas use English function names and commas; your regional settings may require semicolons instead. Save a copy before experimenting.
Enter five daily amounts
Use A1:B6 for this small record. Enter the numbers exactly as shown, including the zero for Wednesday. Zero means the day was recorded and no amount was earned.
| Day | Amount |
|---|---|
| Monday | 120 |
| Tuesday | 80 |
| Wednesday | 0 |
| Thursday | 150 |
| Friday | 50 |
Keep the summary below or beside the data so you can see the input range. In D1 put Metric and in E1 put Result. Labels such as total and recorded-day average are more useful than leaving a number without an explanation.
Calculate the total and average
In E2 enter =SUM(B2:B6). The result is 400. SUM adds numeric values in the range. In E3 enter =AVERAGE(B2:B6). The result is 80 because 400 is divided by five numeric entries, including Wednesday's zero.
Do not include your summary cell in its own input range. A formula such as =SUM(B2:B7) entered into B7 creates a circular reference because the total depends on itself. Place the total outside the data and select the range carefully.
Find the lowest, highest and numeric count
=MIN(B2:B6) returns 0. =MAX(B2:B6) returns 150. =COUNT(B2:B6) returns 5 because it counts numeric cells in the range. These checks help describe the average. An average of 80 could come from five identical days or from very different amounts.
COUNT does not count ordinary text entries. COUNTA counts non-empty cells, including text. That makes COUNTA useful for checking labels or entries, but it is not a substitute for a numeric count. Dates are stored as numbers in Excel, so COUNT can count date cells too.
Compare a zero with a missing value
On a copy of the sheet, clear B4, the Wednesday amount. SUM remains 400. COUNT becomes 4. AVERAGE becomes 100 because it now divides by four numeric entries. MIN becomes 50 because the zero is no longer there.
Neither average is automatically the right business answer. The correct treatment depends on whether Wednesday was a genuine zero or an unrecorded day. A blank should not quietly stand for both meanings. Put notes or status columns beside incomplete records when needed.
Restore B4 to zero after this experiment. Your original five-day average should return to 80. Testing a controlled change is a useful way to understand a function rather than memorising its name.
Recognise numbers stored as text
If someone types an apostrophe before a number, such as '80, Excel can store it as text. In a range reference, SUM and AVERAGE normally ignore that text value. A total can therefore be too low without producing an obvious formula error.
Check suspect cells through the formula bar and available error indicators. Convert the text only after checking that it really represents a number. An ID such as 00123 may need to remain text so its leading zeros are preserved.
Formatting a text cell as Number does not always convert the existing content into a numeric value. Re-entering a small practice value is a simple test. For imported datasets, use a deliberate conversion process and compare counts and totals before and after.
Try it yourself
Add Saturday with an amount of 100 in row 7. Update the summary ranges to include B7. What are the new SUM, AVERAGE, MIN, MAX and COUNT results? Explain why the average is not the same as before.
Check your answer
Using B2:B7, SUM is 500, AVERAGE is approximately 83.3333, MIN is 0, MAX is 150 and COUNT is 6. A two-decimal display would show the average as 83.33 without necessarily rounding the stored value.
The new amount is above the old average of 80, so it raises the six-day average. If you still get 400 and 80, inspect the formulas: their ranges may still end at B6.
Keep learning: Return to the Excel learning path to choose your next lesson.