SUM, AVERAGE, COUNT, MIN and MAX
Updated · Works in: all current Excel versions, Google Sheets · Every example checked before release
Short answer
=SUM(C2:C9) adds a range, AVERAGE averages it, MIN/MAX find the extremes. COUNT counts only numbers; COUNTA counts every non-empty cell, text included. All of them skip empty cells.
Most spreadsheet work starts with five functions. They all take a range like C2:C9 and they all skip empty cells; the differences are in how they treat text.
SUM and AVERAGE over a range
SUM(C2:C9) adds every number from C2 to C9. AVERAGE(C2:C9) divides that by how many numbers there are. Text and empty cells are ignored by both.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
| 10 | Total | $95,200 | $98,150 | $103,100 |
COUNT vs COUNTA
COUNT(A2:A13) counts cells holding numbers; COUNTA(C2:C13) counts every non-empty cell, text included. COUNT on a column of names is 0.
| A | B | C | D | E | F | G | H | I | J | K | |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | Order | Date | Customer | Product | Category | Qty | Price | Status | Orders | 12 | |
| 2 | 1001 | 2 Mar 2026 | Mara Okafor | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 3 | 1002 | 2 Mar 2026 | Jonas Lind | Terracotta pot | Pots | 3 | $12.50 | Shipped | |||
| 4 | 1003 | 3 Mar 2026 | Priya Raman | Snake plant | Plants | 2 | $22.00 | Shipped | |||
| 5 | 1004 | 4 Mar 2026 | Tomás Ruiz | Watering can | Tools | 1 | $18.00 | Pending | |||
| 6 | 1005 | 5 Mar 2026 | Elif Demir | Fern | Plants | 4 | $9.50 | Shipped | |||
| 7 | 1006 | 5 Mar 2026 | Mara Okafor | Potting mix | Soil | 2 | $7.25 | Returned | |||
| 8 | 1007 | 6 Mar 2026 | Kenji Mori | Pruning shears | Tools | 1 | $24.00 | Shipped | |||
| 9 | 1008 | 7 Mar 2026 | Ines Varga | Ceramic pot | Pots | 2 | $19.00 | Pending | |||
| 10 | 1009 | 8 Mar 2026 | Jonas Lind | Monstera | Plants | 1 | $34.00 | Shipped | |||
| 11 | 1010 | 9 Mar 2026 | Sofia Rossi | Fern | Plants | 6 | $9.50 | Shipped | |||
| 12 | 1011 | 10 Mar 2026 | Priya Raman | Potting mix | Soil | 5 | $7.25 | Shipped | |||
| 13 | 1012 | 11 Mar 2026 | Leo Brandt | Snake plant | Plants | 1 | $22.00 | Pending |
MIN and MAX
MIN and MAX return the smallest and largest number. Combine them for a range: MAX(…) - MIN(…).
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
| 10 | ||||||
| 11 | July spread | $6,500 |
Check yourself
One real exercise from the “Ranges and functions” unit. Checked on our server; no account needed.Total each month: write a SUM in C10 for July. We'll copy it across to August and September.
- Use SUM
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 |
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 |
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 |
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 |
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 |
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
| 10 | Total |
Common mistakes
Using COUNT to count names: it only counts numbers. Use COUNTA.
Numbers stored as text are skipped by SUM, so the total comes out too low.
Including the total row inside the SUM range, which double-counts.
Questions
Does AVERAGE count empty cells as zero?+
No. Empty cells are skipped. A cell containing 0 does count.
How do I sum only some rows?+
Use SUMIF or SUMIFS, which add the rows that match conditions.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.