formulastreak

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.

Q3 sales team · edit C10 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultC10 = $95,200
ABCDEF
1RepRegionJulAugSepTarget
2Amara OseiNorth$12,400$13,100$11,800$36,000
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$14,500$15,800$15,100$42,000
10Total$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.

Leaf & Loam orders · edit K1 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultK1 = 12
ABCDEFGHIJK
1OrderDateCustomerProductCategoryQtyPriceStatusOrders12
210012 Mar 2026Mara OkaforMonsteraPlants1$34.00Shipped
310022 Mar 2026Jonas LindTerracotta potPots3$12.50Shipped
410033 Mar 2026Priya RamanSnake plantPlants2$22.00Shipped
510044 Mar 2026Tomás RuizWatering canTools1$18.00Pending
610055 Mar 2026Elif DemirFernPlants4$9.50Shipped
710065 Mar 2026Mara OkaforPotting mixSoil2$7.25Returned
810076 Mar 2026Kenji MoriPruning shearsTools1$24.00Shipped
910087 Mar 2026Ines VargaCeramic potPots2$19.00Pending
1010098 Mar 2026Jonas LindMonsteraPlants1$34.00Shipped
1110109 Mar 2026Sofia RossiFernPlants6$9.50Shipped
12101110 Mar 2026Priya RamanPotting mixSoil5$7.25Shipped
13101211 Mar 2026Leo BrandtSnake plantPlants1$22.00Pending

MIN and MAX

MIN and MAX return the smallest and largest number. Combine them for a range: MAX(…) - MIN(…).

Q3 sales team · edit C11 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultC11 = $6,500
ABCDEF
1RepRegionJulAugSepTarget
2Amara OseiNorth$12,400$13,100$11,800$36,000
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$14,500$15,800$15,100$42,000
10
11July 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
Select C10 and type = to start its formula (in the cell or the formula bar)
ABCDEF
1RepRegionJulAugSepTarget
2Amara OseiNorth$12,400$13,100$11,800$36,000
3Ben FischerSouth$9,800$10,250$12,100$30,000
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$14,500$15,800$15,100$42,000
10Total

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.

Practise it now