Excel · Lesson 2 of 8

SUM, AVERAGE, COUNT & ROUND

The everyday functions for totals, averages, counts and rounding.

SUM(range) — total. Alt+= inserts AutoSum.
AVERAGE(range) — mean of numbers.
MIN / MAX — smallest / largest.
COUNT — counts numbers; COUNTA — counts non-empty cells; COUNTBLANK — empty cells.
ROUND(x, digits) — round properly; ROUNDUP / ROUNDDOWN force a direction.

Formatting a cell to show 2 decimals does not change the stored value — ROUND does. This matters when totals must match invoices.

Sample sheet & formulas

Type these in Excel or Google Sheets

     A          B
1    Employee   Overtime hrs
2    Anu        12.5
3    Rahul      8
4    Fathima    (blank)
5    Joseph     15.25

B7:  =SUM(B2:B5)
B8:  =AVERAGE(B2:B5)
B9:  =MAX(B2:B5)
B10: =COUNT(B2:B5)
B11: =COUNTA(A2:A5)
B12: =ROUND(B8, 1)

Results

B7  = 35.75
B8  = 11.9166…   (blank cell is ignored)
B9  = 15.25
B10 = 3
B11 = 4
B12 = 11.9

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

AVERAGE ignores blank cells.
COUNT = numbers only; COUNTA = anything non-empty.
ROUND changes the value; formatting only changes the display.

Quick check

Which function counts non-empty cells including text?

COUNT COUNTA SUM COUNTBLANK

Practice task

For a list of 10 monthly sales figures, show the total, average (rounded to 0 decimals), best month and number of months with data.

Interview corner

Why might a SUM total differ from a calculator total?

Cells may show rounded values while storing more decimals, or some numbers may be stored as text and be ignored. Use ROUND and convert text to numbers.

How do you quickly check sum/average of a selection?

Select the cells and read the status bar at the bottom — it shows Sum, Average and Count without a formula.