Skip to content
Tutorials Center
My learning

Topics

Lesson 7 of 29 ยท Formulas and Functions

SUM, AVERAGE, MIN, MAX and COUNT

Summarize ranges in Google Sheets with SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, COUNTBLANK and the ROUND family, and learn what each one ignores.

A spreadsheet earns its keep when it turns a column of numbers into one useful answer: the total, the average, the biggest value, how many rows you have. Google Sheets does this with a handful of aggregate functions that take a range and return a single number.

In this lesson you will use SUM, AVERAGE, MIN, MAX, MEDIAN, the three counting functions (COUNT, COUNTA, COUNTBLANK) and the rounding family. You will also learn the part that surprises beginners: what these functions silently ignore. If you need a refresher on typing formulas, see Your First Formulas.

One range in, one number out

SUM Adds every number.
AVERAGE Sum divided by count.
MAX The largest number.
MIN The smallest number.
1 / 2

The everyday aggregates

Suppose your sales figures sit in B2:B8. These formulas each give one answer:

TEXT
=SUM(B2:B8)        total of all numbers
=AVERAGE(B2:B8)    mean value
=MIN(B2:B8)        smallest value
=MAX(B2:B8)        largest value
=MEDIAN(B2:B8)     middle value when sorted

A range such as B2:B8 means "every cell from B2 down to B8". You can also give several arguments and mix ranges with single cells and plain numbers: =SUM(B2:B8, D2, 100) adds the range, the cell D2 and the number 100. A whole column is written B:B and grows as you add rows, which is handy for running totals.

Two shortcuts save typing. Select a column of numbers and open the ฮฃ (Functions) button in the toolbar to insert SUM, AVERAGE, COUNT, MAX or MIN in one click. And when you select cells, the bottom-right corner of the window shows a quick sum, average and count without any formula at all.

Everything here is real Google Sheets behavior, but the Sheets app itself cannot run on this page. The code blocks marked "stand-in" are small JavaScript programs that imitate what the functions do, so you can experiment with the rules.

What the functions ignore

This is the heart of the lesson. When a range contains text or empty cells, the aggregates skip them instead of failing:

  • SUM, AVERAGE, MIN, MAX and MEDIAN only look at numbers inside a range. Text (such as "n/a") and blank cells are left out.
  • AVERAGE divides by the count of numbers, not the count of cells. A blank cell does not pull the average down; a cell containing 0 does.
  • Numbers that were typed as text (for example a cell showing 42 but left-aligned, because it was pasted as text) are also skipped. That is a common reason for a total that looks too small. See Entering and Editing Data for how Sheets decides what is a number.

The stand-in below keeps a column of mixed cells and applies the same rules. Real Sheets does all of this for you; the code only shows the logic.

JS Stand-in: aggregates skip text and blanks

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Notice that the text "42" is not counted and that the 0 is. Change the 0 to null and watch the average rise.

COUNT, COUNTA and COUNTBLANK

Counting sounds simple until you ask what to count:

Formula Counts Typical use
=COUNT(A2:A20) Cells containing numbers (dates count too) How many scores were entered
=COUNTA(A2:A20) Cells that are not empty (numbers, text, even FALSE) How many names are listed
=COUNTBLANK(A2:A20) Empty cells, plus cells whose formula returns "" How many answers are missing

For any range, COUNTA + COUNTBLANK equals the number of cells. COUNT can be smaller than COUNTA, and the gap is the text cells. A cell holding only a space looks empty but is not blank: COUNTA counts it and COUNTBLANK does not. The text functions lesson shows how TRIM helps with that.

JS Stand-in: three counting rules

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Finding the biggest and smallest, with position

MAX and MIN return values, not positions. To find which row holds the highest value, combine them with a lookup, covered later in XLOOKUP, INDEX and MATCH. For ranking, LARGE(range, 2) returns the second-largest value and SMALL(range, 3) the third-smallest. MEDIAN is less affected by one extreme value than AVERAGE, so it is often the better summary for salaries or house prices.

Rounding: ROUND and its relatives

Display formatting and rounding are different things. Formatting a cell to two decimals only changes how the number looks; the full value is still used in calculations. A rounding function changes the value itself.

Formula Result Rule
=ROUND(3.14159, 2) 3.14 Nearest, 2 decimals
=ROUND(2.5) 3 Halves round away from zero
=ROUND(-2.5) -3 Away from zero, not toward it
=ROUND(1234, -2) 1200 Negative digits round to hundreds
=ROUNDUP(4.1, 0) 5 Always away from zero
=ROUNDDOWN(4.9, 0) 4 Always toward zero
=TRUNC(-4.9) -4 Drops the decimals
=INT(-4.1) -5 Rounds down to the next lower integer
=CEILING(7.2, 0.5) 7.5 Up to a multiple
=FLOOR(7.8, 0.5) 7.5 Down to a multiple
=MROUND(17, 5) 15 To the nearest multiple

Money is where rounding bites. If an invoice line is =ROUND(price * quantity * 1.0825, 2), each line is rounded to cents before the lines are added, so the total matches what you would print on paper. Rounding only the final total can differ by a cent.

JS Stand-in: the ROUND family

Press Run (or Ctrl + Enter) to see the console output here.

No output.

Putting it together

A typical summary block under a table looks like this:

TEXT
Total sales     =SUM(B2:B8)
Average sale    =ROUND(AVERAGE(B2:B8), 2)
Best day        =MAX(B2:B8)
Entries         =COUNT(B2:B8)
Missing         =COUNTBLANK(B2:B8)

Nesting ROUND around AVERAGE tidies the result to cents. Functions can be nested to many levels; read them from the inside out. If one of them returns an error, see Understanding Formula Errors.

Use B:B style whole-column ranges only for the aggregate functions. They skip the header text automatically, which is why =SUM(B:B) works even though B1 says "Sales".

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A range holds 10, 20, an empty cell and the text "n/a". What does AVERAGE return?

    AVERAGE skips blank and text cells, so it divides 30 by 2 numbers and returns 15.
  2. 2. Which function counts cells that contain any value, including text?

    COUNTA counts non-empty cells of any kind. COUNT only counts numbers and COUNTBLANK counts empty cells.
  3. 3. What does =ROUND(-2.5) return in Google Sheets?

    Sheets rounds halves away from zero, so -2.5 becomes -3.
  4. 4. A cell is formatted to show 2 decimals but holds 3.14159. What does =A1*2 use?

    Formatting changes only the display. The stored value is unchanged, so calculations use all its digits.

Summary

  • SUM, AVERAGE, MIN, MAX and MEDIAN turn a range into one number and skip text and blank cells.
  • AVERAGE divides by the number of numeric cells, so 0 counts and blanks do not.
  • COUNT counts numbers, COUNTA counts non-empty cells, COUNTBLANK counts empty ones.
  • ROUND, ROUNDUP, ROUNDDOWN, TRUNC, INT, CEILING, FLOOR and MROUND change the actual value, unlike number formatting.
  • Numbers stored as text are skipped, a frequent cause of totals that look wrong.

Next: Working with Text