Adding up with SUM

If one cell in a column holds the text n/a, =B2+B3+B4 shows #VALUE!, while =SUM(B2:B4) skips the text and adds the rest.

Your first function

A function is a named calculation that takes inputs in brackets. SUM adds numbers. Its inputs, called arguments, can be numbers, cell references, ranges or any mix: =SUM(4,6), =SUM(B2:B13) and =SUM(B2:B13,D2) all work. Commas separate the arguments. Microsoft's page says Excel's SUM takes up to 255 of them, and Google's help says Sheets accepts any number. In practice one or two ranges are enough, because a single range can cover thousands of cells.

Writing =B2+B3+B4+B5+B6 gives the same total as =SUM(B2:B6) when every cell holds a number, but Microsoft lists reasons to prefer SUM. The long version is easy to mistype. It breaks with #VALUE! if any of the cells holds text, because + must turn every piece into a number and cannot turn n/a into one. SUM ignores text values in a range and adds only the numbers. And when you insert a row inside the range, SUM grows to include it, while a chain of + signs does not.

Skipping text is helpful and dangerous at the same time. If a figure was typed as text by mistake, such as a number pasted with a stray space or apostrophe, SUM leaves it out without any warning and the total is simply too small. The left alignment from the previous unit is the clue to look for, and the lab's panel shows the kind of every value a formula used.

Colons and commas

The colon and the comma do different jobs. B2:B6 means every cell from B2 to B6, five cells. B2,B6 means just those two cells. Microsoft calls them the range operator and the union operator. So =SUM(B2:B3,B6) adds three cells, B2, B3 and B6. One more detail: in some languages and regions the comma is used as the decimal mark, and there the spreadsheet expects semicolons between arguments, as in =SUM(B2:B3;B6). The rule is the same, only the separator changes.

Both programs have a sum button marked with the Greek letter Σ. In Excel it is AutoSum on the Home tab; in Google Sheets it is in the Functions menu on the toolbar. Select the cell under a column of numbers, press it, and the program proposes a range. Check the proposed range before pressing Enter, because it stops at the first empty cell or label it meets. Selecting a block of numbers also shows their sum at the bottom of the window, which is handy for a quick look without writing anything.

A good habit is to keep totals out of the range they add up. If the total for B2:B6 sits in B7 and the formula is =SUM(B2:B7), it includes itself, which is a circular reference and produces an error or a warning. Put a label such as Total in the cell to the left of the result so readers know what the number is, and leave the column of figures with nothing but figures in it. Then both SUM and anyone reading the sheet can trust the column.