How to Sum a Column in Google Sheets (SUM Function)

SUM adds numbers together: individual values, cell references, whole ranges, or any mix. Adding up a column is the job it does most, so that’s where this page starts.

How to Sum a Column

Click the empty cell under your numbers and type SUM with the column’s range:

=SUM(B2:B20)

That adds every number from B2 to B20. Blank cells count as nothing, and text is skipped without an error, so a column with a stray “n/a” still totals.

Sum a column that keeps growing

If you add rows every week, a fixed range like B2:B20 stops including them once you pass row 20. Leave off the last row number instead:

=SUM(B2:B)

That adds B2 all the way to the bottom of the column, including rows added later.

See a sum without a formula

Select the cells you want to add. Google Sheets shows their sum in the bottom-right corner, for example Sum: $1,556.00. Click that box to switch it to average, count, min or max. It’s the quickest check, and nothing is added to the sheet.

Parameters

More SUM Examples

Sum multiple ranges

=SUM(B2:B20, D2:D20)

Returns the combined total of both ranges. Add as many ranges as you need, separated by commas.

Mix ranges and single values

=SUM(B2:B10, 50, C2)

Adds the total of B2:B10, plus 50, plus the value in C2.

Sum with a condition

To add only some rows, for example sales from one region, use SUMIF or SUMIFS instead:

=SUMIF(A2:A20, "West", B2:B20)

Sum only visible rows

SUM includes hidden rows. To total only what’s on screen after hiding or filtering rows, use SUBTOTAL with code 109:

=SUBTOTAL(109, B2:B20)

See hide and unhide rows for how the two compare.

Common Problems

The total is too low. Some numbers are stored as text, and SUM skips text inside a range. We tested a column of 100, 50 and 25 where the 50 was text: SUM returned 125, not 175. Text numbers usually sit on the left side of the cell. Fix them with Format > Number > Number, or convert with =VALUE().

#REF! Either the range includes the formula’s own cell (see the warning above), or a row or column the formula pointed at was deleted. Check the range in the formula bar.

The total doesn’t update for new rows. The range ends before the new rows. Use an open-ended range like B2:B, or insert new rows inside the existing range rather than below it.

Tips

Use SUM instead of chaining cells with +. =SUM(B2:B100) is shorter than =B2+B3+B4..., and it keeps working when you insert rows inside the range.

To subtract one column’s total from another, subtract two SUMs: =SUM(B2:B20)-SUM(C2:C20). More in how to subtract in Google Sheets.