SUM, COUNT, AVERAGE, MIN and MAX
Every report Nisha reads ends in a summary row: how many, how much in total, what is typical, and the best and worst. Five functions build that row, and together they answer most "quick numbers" questions an analyst hears. Here is the week of cold-coffee sales, in B2:B8:
| A | B | |
|---|---|---|
| 1 | day | cups |
| 2 | Mon | 40 |
| 3 | Tue | 38 |
| 4 | Wed | 36 |
| 5 | Thu | 43 |
| 6 | Fri | 46 |
| 7 | Sat | 95 |
| 8 | Sun | 38 |
The rule
Sum adds, count counts numbers, average is sum over count.
| formula | what it does | result |
|---|---|---|
=SUM(B2:B8) |
adds every number | 336 |
=COUNT(B2:B8) |
counts cells that hold numbers | 7 |
=AVERAGE(B2:B8) |
SUM ÷ COUNT | 48 |
=MIN(B2:B8) |
the smallest number | 36 |
=MAX(B2:B8) |
the largest number | 95 |
How they treat awkward cells matters more than the formulas themselves:
- Blank cells and text are skipped by all five. A blank does not count as a day, and does not drag the average down.
- Zero is a number. It is counted, and it lowers the average.
COUNTignores text. To count every non-empty cell, including text such as names, useCOUNTA. To count blanks, useCOUNTBLANK.
Each one has a conditional sibling for "only the rows that match": COUNTIF, SUMIF, AVERAGEIF. For example, =SUMIF(D2:D6, "C01", F2:F6) totals the amount column for cold-coffee orders only.
The shortcut
Before trusting a summary row, check COUNT first. If COUNT is not the number of rows you expect, blanks or text are hiding in the column, and every other result is quietly affected.
Check COUNT
=COUNT(B2:B8)shows 6, not 7. Something is missing.
The trap: numbers stored as text. A value typed as '45 or imported with a stray space looks like a number but is skipped by SUM and COUNT. A COUNT lower than your row count is the tell.
Drills
Answer fast — trust the rule.
What does =AVERAGE(B2:B8) return for the week?
Saturday's 95 is deleted and the cell left blank. What does =AVERAGE(B2:B8) return, to one decimal place?
What does =MAX(B2:B8) - MIN(B2:B8) return?
A1:A5 holds 12, "n/a", 0, (blank), 8. What does =COUNT(A1:A5) return?
Which formula totals the amount column (F2:F6) for cold-coffee orders (code C01 in D2:D6) only?
Remember
- Sum adds, count counts numbers, average is sum over count.
- Shortcut: check COUNT first; a surprise there means blanks or text in the column.
- Trap: a zero is counted, a blank is not, and numbers stored as text are invisible.