To add a column in Google Sheets, enter =SUM(B2:B6) in a cell outside that range. SUM adds the numeric values and returns one total. You can also combine rows, blocks, and separate cells.
Add a Column of Numbers
Enter this dataset starting in A1. Leave cells marked “(blank)” empty.
| Item | Amount |
|---|---|
| Pens | 12 |
| Folders | 18 |
| Paper | 5 |
| Tape | 9 |
| Labels | 6 |
Enter this formula in E2:
=SUM(B2:B6)The result is 50: 12 + 18 + 5 + 9 + 6. Changing an amount updates the total automatically.
You can type the range or select its cells while entering the formula. The colon in B2:B6 includes both endpoints and every cell between them.
Keep the total outside its own range. Putting this formula inside B2:B6 creates a circular reference. If the total is in B7, the range should stop at B6.
SUM Function Syntax
=SUM(value1, [value2, ...])value1 is the first number, cell, or range. Additional arguments let you combine more numbers and ranges. The square brackets indicate optional arguments; do not type them into your formula.
Add a Row or a Rectangular Block
Enter this dataset starting in A12. Leave cells marked “(blank)” empty.
| Region | Jan | Feb |
|---|---|---|
| East | 100 | 120 |
| West | 80 | 95 |
| North | 60 | 70 |
=SUM(B13:C15)This returns 525 by adding all six monthly figures. SUM accepts a rectangle, not just a single column. The row labels in column A are outside the range.
For one row, select only that row’s numeric cells. For an entire data column, an open-ended range can include future entries, provided the total is in a different column and no other numbers belong below.
Add Cells That Are Not Next to Each Other
=SUM(B2,B4)Using the first dataset, this returns 17, combining Pens and Paper. Separate references with commas. A colon would include the intervening Folders amount as well.
Handle Text, Blanks, and Errors
Enter this dataset starting in A20. Leave cells marked “(blank)” empty.
| Entry | Amount |
|---|---|
| A | 10 |
| B | Pending |
| C | (blank) |
| D | 25 |
| E | 0 |
=SUM(B21:B25)The result is 35. Text and blank cells in a referenced range are skipped. Zero remains a valid number, even though adding it does not change the total.
A number stored as text can therefore leave a total unexpectedly low. Convert that cell to a real number with VALUE or correct the source data; changing its display format alone may not convert it.
Direct arguments behave differently: =SUM(5,"10") returns 15, while =SUM("ten",5) returns #VALUE!. Error values are not harmless text; =SUM({5,NA(),10}) returns #N/A.
Sum Rows That Meet a Condition
Enter this dataset starting in A30. Leave cells marked “(blank)” empty.
| Region | Sales |
|---|---|
| East | 100 |
| West | 40 |
| East | 120 |
| North | 50 |
| East | 90 |
=SUM(FILTER(B31:B35,A31:A35="East"))The result is 310. FILTER first keeps the three East sales; SUM then adds 100, 120, and 90. With no matching rows, this combination returns #N/A.
For one straightforward condition, SUMIF is usually easier:
=SUMIF(A31:A35,"East",B31:B35)This also returns 310. Use SUMIFS when several conditions must all be satisfied.
Filtering rows through the sheet interface is different from using FILTER inside a formula. A normal SUM still includes those source numbers. Use SUBTOTAL when the total should follow visible rows.
Other Google Sheets articles you may also like