Skip to content
Tutorials Center
My learning

Topics

Lesson 12 of 93 ยท Formulas Fundamentals

SUM, AVERAGE, COUNT, MIN and MAX

Master the everyday Excel summary functions: SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, MIN, MAX, MEDIAN and MODE, and how they treat text and blanks.

The first functions almost everyone learns are the ones that summarize a list of numbers: add them up, average them, count them, find the smallest and the largest. They look simple, and they are, but they hide rules about text, blanks and zeros that decide whether your totals are right. Many wrong reports come from a function quietly skipping a cell the author thought it would include.

In this lesson you will use SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK, MIN, MAX, MEDIAN and MODE, see exactly which cells each one ignores, and compare the results with a pandas equivalent. The function names and formulas are real Excel; the live editors are a stand-in engine and real pandas.

Nine summary functions

SUM Add the numbers.
AVERAGE Mean of the numbers.
COUNT How many numbers.
MIN and MAX Smallest and largest.

They all work on ranges, lists or both.

1 / 2

SUM and AutoSum

SUM adds every number in the arguments you give it. An argument can be a single cell, a range, a typed number, or any mix, separated by commas:

TEXT
=SUM(B2:B9)            add one range
=SUM(B2:B9, D2:D9)     add two ranges
=SUM(B2:B9, 100)       add a range and a constant
=SUM(B2:B9)/COUNT(B2:B9)

The quickest way to write one is AutoSum: select the cell below a column of numbers (or to the right of a row) and press Alt+= (or click ฮฃ AutoSum on the Home or Formulas tab). Excel guesses the range and highlights it with a moving border. Always check that the highlight covers what you intended, because AutoSum stops at the first gap or text cell it meets. Press Enter to accept.

If you only want a quick look and no formula, select the cells and glance at the status bar at the bottom of the window. It shows the Average, Count and Sum of the selection without writing anything into the sheet. Right-click the status bar to add Min, Max and Numerical Count.

AVERAGE, MIN and MAX

AVERAGE adds the numbers and divides by how many there are. MIN and MAX return the smallest and largest. They take the same kinds of arguments as SUM.

TEXT
=AVERAGE(B2:B9)
=MIN(B2:B9)
=MAX(B2:B9)

The average counts only cells that contain numbers. A blank cell is not a zero: it is skipped, so it neither adds to the total nor to the count. A cell that actually contains 0 is a number and does pull the average down. That difference matters when you leave a cell empty to mean "no data yet" versus typing zero to mean "none".

What the functions ignore

When the argument is a range or a cell reference, these functions look only at cells that hold real numbers. Text, logical values (TRUE/FALSE) and empty cells inside a range are skipped. Numbers stored as text, such as "30" or 30 with a leading apostrophe, are skipped too, which is a classic reason a total comes out too small. (A green triangle in the cell's corner warns you.) Chapter 5 covers fixing numbers stored as text in TEXT, VALUE and Number-as-Text Problems.

If an error such as #N/A sits anywhere in the range, SUM and AVERAGE return that error instead of skipping it. Error values are the subject of Formula Errors and How to Fix Them.

Values you type directly as arguments are treated more strictly: =SUM("30", 5) converts the text to a number and gives 35, but =SUM("abc", 5) returns #VALUE!.

COUNT, COUNTA and COUNTBLANK

Counting needs more precision, because "how many cells" can mean three different things:

Function Counts Example data in a range Result
COUNT Cells containing numbers (dates and times are numbers too) 10, "n/a", empty 1
COUNTA Cells that are not empty (numbers, text, logicals, errors, and formulas that return "") 10, "n/a", empty 2
COUNTBLANK Cells that are empty, or hold a formula returning "" 10, "n/a", empty 1

A handy check: for any range, COUNTA plus COUNTBLANK equals the number of cells. And COUNTA(range) - COUNT(range) tells you how many cells contain something that is not a number, which is a quick way to find text hiding in a numeric column.

MEDIAN and MODE

The mean is not always the best "typical" value. MEDIAN returns the middle value when the numbers are sorted (the average of the two middle values when the count is even). One huge salary pulls the average up but leaves the median almost unchanged, so the median describes skewed data better.

MODE returns the value that occurs most often. If every value is unique, it returns #N/A. When two values tie for most common, MODE returns the one that appears first in the data. Newer workbooks also have MODE.SNGL (the same thing) and MODE.MULT (returns all modes as an array; see Dynamic Arrays and Spilling).

Trying them out

The stand-in engine below holds a column with a number stored as text, a text cell, a blank cell and a logical value, to show exactly what each function does. Column B leaves a blank for missing data; column C uses a zero. Read the output line by line.

JS Summary functions on messy data

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

No output.

Read the results: the column of mixed data has COUNT = 4 but COUNTA = 7, so three non-empty cells are not numbers (the text "30", the text "n/a" and TRUE). SUM therefore reaches only 90 and not 120, because the text "30" is skipped. Compare AVERAGE(B1:B4) and AVERAGE(C1:C4): the same visible data gives different answers depending on whether the gap is empty or a zero.

The same ideas in pandas

If you analyze data with Python, pandas behaves in a very similar way: missing values are skipped by sum and mean, count counts the non-missing values, and median and mode have direct equivalents. The one important difference is that pandas does not silently ignore text; you have to separate the numbers first, which makes the rules visible.

Python Excel's rules written in pandas

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

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The last line prints all modes ([1, 3], sorted) while Excel's MODE would give 3, because 3 occurs first in the data. That detail is easy to miss when you translate between the two tools.

Practical tips

  • Check the range. If AutoSum picks a range that stops early because of a text cell or gap, fix the data or extend the range by hand.
  • Total rows, filters and hidden rows. SUM includes hidden rows. To total only what a filter shows, use SUBTOTAL(109, B2:B9) or AGGREGATE; see Filtering Data.
  • Prefer a labeled input over a hard-coded number inside SUM(B2:B9, 100). A bare 100 hides an assumption.
  • Do not add by hand (=B2+B3+B4) when SUM(B2:B4) exists: inserted rows inside a range are picked up by SUM but not by a chain of +.
  • Whole columns work in SUM and friends (=SUM(B:B)), as long as no stray numbers or totals sit in the column.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A range holds 4, 8, the text "n/a", and an empty cell. What does =AVERAGE(range) return?

    AVERAGE skips the text and the empty cell, so it uses only 4 and 8. (4 + 8) / 2 = 6.
  2. 2. For the same range (4, 8, "n/a", empty), what do COUNT and COUNTA return?

    COUNT counts only numbers (4 and 8), so 2. COUNTA counts every non-empty cell, including the text, so 3. The empty cell is counted by neither.
  3. 3. Which shortcut inserts an AutoSum?

    Alt+= inserts SUM for the nearby numbers. F4 changes reference types, and Ctrl+T creates a table.
  4. 4. Why might MEDIAN describe salaries better than AVERAGE?

    One very high value drags the average upward but only moves the middle of the sorted list by a position, so the median stays representative.
  5. 5. =MODE(A1:A5) returns #N/A. What does that mean?

    MODE returns #N/A when every number is unique, since there is no value that repeats.

Summary

  • SUM, AVERAGE, MIN and MAX use only real numbers in a range; text, logical values, blanks and numbers stored as text are skipped.
  • A blank is not a zero: it is skipped by AVERAGE, while a typed 0 pulls the average down.
  • COUNT counts numbers, COUNTA counts anything not empty, COUNTBLANK counts empty cells.
  • MEDIAN gives the middle value and resists extreme values; MODE gives the most frequent value (first on ties, #N/A if none repeats).
  • Alt+= inserts AutoSum; the status bar shows quick totals without a formula.
  • An error inside the range makes SUM and AVERAGE return that error.

Next: Formula Errors and How to Fix Them.