SUM practice exercises

Add up a column or a range with SUM, the first function everyone learns. These drills cover a single column, several ranges and the AutoSum shortcut.

Start the first exercise

How SUM works

SUM adds up numbers. Give it one or more arguments, and each one can be a single cell, a range or a plain number. Excel accepts up to 255 arguments.

=SUM(B2:B5) adds the four cells in the range, so units of 12, 8, 15 and 5 return 40. When the numbers sit in two places, give SUM two ranges: =SUM(B2:B3,E2:E3). To total a whole block, use one range that covers it, like =SUM(B2:D4).

SUM skips text inside a range, so a cell that says "n/a" doesn't break the total. Adding the cells with plus does: =B2+B3 returns #VALUE! as soon as one of them is text. Watch out for numbers stored as text, though. SUM skips those too, and the total comes out too low. The exercises below cover a single column, a block of several columns, two separate ranges and a range that contains text.

  1. 1Add up a column with SUMIn B6, use SUM to total the Units in B2:B5.
  2. 2SUM a block of cellsIn F2, total the sales of every region in every quarter with one SUM over B2:D4.
  3. 3SUM two separate rangesIn G2, add up Rent and Fun for both months. They are not next to each other, so give SUM two ranges separated by a comma.
  4. 4SUM skips text that + can'tIn B7, add up the sales in B2:B6. Aalborg hasn't reported yet and says n/a, so use SUM, which skips text, rather than adding the cells with +.

Functions in this topic

Read the guide

Questions

How do I add up a column in Excel without typing the range?

Select the cell right below the numbers and click AutoSum on the Home tab, or press Alt+=. Excel suggests the range of numbers above, and you confirm with Enter. AutoSum stops at the first empty cell, so check that the highlighted range covers everything before you press Enter.

Why does SUM show 0 or a total that is too low?

The numbers are probably stored as text, and SUM skips text. They often sit on the left side of the cell and have a small green triangle in the corner. Select the cells, click the warning icon and choose Convert to Number. Once they are real numbers, SUM counts them again.

How do I sum the same cell across several sheets?

Use a 3D reference: =SUM(Jan:Mar!B2) adds cell B2 from every sheet between Jan and Mar, including the ones in the middle. The sheets must sit next to each other in the tab bar. If you drag a sheet out of that stretch, it drops out of the total.