The SUM formula in Excel, and four more that come with it

SUM adds up a range of cells. It is the first formula most people learn, and the four in this lesson, AVERAGE, MAX, MIN and COUNT, work the same way. The sheet above is the lesson; this page explains it, with an example worked out by the same formula engine.

What SUM does

=SUM(number1, [number2], …)

Most of the time you give it one range: C2:C8 means every cell from C2 down to C8, in the same column. You can add more, separated by commas, and SUM adds them all: =SUM(C2:C8,E2:E8). Empty cells and text inside the range are skipped, so a heading or a blank row doesn't break the total.

A formula always starts with =. Without it Excel stores what you typed as text, and the cell shows the words instead of the answer.

A worked example

This sheet comes from the lesson, set in the Coffee bar: cups sold at each branch in one day.

In C9, =SUM(C2:C8) adds the numbers above it and gives 2,988. Add a branch inside the range later and the total updates by itself. That is the whole point of a formula over a number typed in by hand.

AVERAGE, MAX, MIN and COUNT

Same range, a different question each time.

AVERAGE

Adds the numbers and divides by how many there are: =AVERAGE(C2:C8) gives 427. Empty cells are left out of the count, but zeros are counted, so a 0 pulls the average down and a blank doesn't. The column is set to show whole numbers, so the grid rounds the answer for display; the cell keeps every decimal.

MAX and MIN

The highest and the lowest: =MAX(C2:C8) gives 585, and =MIN(C2:C8) gives 276. They read the whole column however long it gets, which beats scanning it by eye.

COUNT

How many cells hold a number: =COUNT(C2:C8) gives 7. It ignores text. To count every cell that isn't empty, names included, use COUNTA: =COUNTA(A2:A8) gives 7.

What goes wrong

Typing the answer. Working out the total yourself and typing it in gives the right number today and a wrong one the moment a figure changes. The lesson refuses a typed number, even a right one, and asks for the formula.

A range that misses a row. Stop the range one row short and the last row is left out of the total, with nothing in the cell to tell you; the lesson names this one when you do it. The lesson also catches answers that are only right by chance: it runs your formula again on changed numbers, so a MAX over two cells picked by eye gives itself away.

The total inside its own range. A SUM whose range runs down into its own cell asks the cell to include itself. Excel warns about a circular reference and the answer can't be trusted. The range ends on the row above the total.

Numbers stored as text. Data pasted from another system sometimes arrives as text that only looks like numbers. SUM skips text, so the total comes out too low. In Excel such cells sit on the left of the cell instead of the right, often with a small green triangle in the corner.

The lesson, step by step

1. 1 Add up the day

   In C9: =SUM(C2:C8) gives 2,988.

   Named mistake: =SUM(C2:C7) gives 2,436. That stops one row early, at C7. The range goes down to C8.

2. 2 Find a typical branch

   In C10: =AVERAGE(C2:C8) gives 427.

   Named mistake: =AVERAGE(C2:C9) gives 747. That takes in the total in C9. Stop at C8.

3. 3 Find the busiest branch

   In C11: =MAX(C2:C8) gives 585.

   Named mistake: =MAX(C2:C9) gives 2,988. That takes in the total in C9, the biggest number on the sheet. Stop at C8.

4. 4 Find the quietest branch

   In C12: =MIN(C2:C8) gives 276.

5. 5 Count them

   In C13: =COUNT(C2:C8) gives 7.

   Named mistake: =COUNT(C2:C7) gives 6. That stops one row early, at C7. The range goes down to C8.

   Named mistake: =COUNT(C2:C9) gives 8. That takes in the total in C9. Stop at C8.

In real Excel

AutoSum writes the SUM for you: select the cell under a column of numbers and press Alt + = on Windows, or Cmd + Shift + T in Excel for Mac, then Enter. It guesses the range from the numbers above, so check the guess before you press Enter. The shortcuts lesson practises it.

For a quick look without a formula, select some cells: the bar at the bottom of the Excel window shows their sum, average and count.

Keep going

1. 2
   Next up is Copying: absolute referenceCopy a formula down a column, then lock one cell with $ so it stays put.
2. 2
   Copying: absolute referenceCopy a formula down a column, then lock one cell with $ so it stays put.
3. 10
   Month end: month-end reportThe final sheet: a real report asked in plain words, no formulas shown.