How to Sum a Column in Google Sheets
Use SUM to add every number in a column or a defined range. A finite range is safest for a fixed table, while an open-ended range can include new rows automatically when the total sits outside that range.
Reviewed by the SheetsX team
Adds five known revenue cells without including the header or Total row.
Includes new numeric values appended below B2 when the formula is stored outside the summed range.
SheetsX checked the five fictional monthly values and confirmed that only B7 changed.
What formula sums a column in Google Sheets?
Enter =SUM(B2:B6) to add cells B2 through B6. For a column that will grow, put the total outside column B and use =SUM(B2:B). Avoid placing =SUM(B:B) inside column B because the formula would include itself and create a circular reference.
Watch SheetsX sum a column and verify the total
The Agent adds five monthly revenue values with =SUM(B2:B6), replaces only the incorrect Total value, updates its fill color, and verifies that no month, revenue figure, or header changed.
The recording uses fictional revenue data. It shows the exact English prompt, the formula =SUM(B2:B6), the calculated total 106000, and the final no-other-cells-changed verification.
“Fix the incorrect Total in B7. Replace the hardcoded zero with =SUM(B2:B6). Do not change any month names, revenue values, or header labels. Remove the red fill from B7, use a pale green fill, and verify the displayed result is 106000. Reply in English and confirm that no other cells changed.”
How to sum a column in Google Sheets
Select an empty result cell, enter the SUM range, and verify that the range includes the intended numeric rows without including the result cell itself.
- 1
Choose the result cell
Click an empty cell below the data or in a separate summary area. Keep it outside any open-ended range you plan to sum.
- 2
Enter the SUM formula
Type =SUM(B2:B6) for a fixed set of rows, replacing B2:B6 with your numeric range.
- 3
Press Enter
Google Sheets evaluates the range and displays the total. In the demo, five monthly values return 106000.
- 4
Check the range and future rows
Confirm that the header and Total row are excluded. Use an open-ended range only when the formula is outside that range.
SUM formulas for whole and partial columns
Pick a bounded range for a fixed table, an open-ended range for incoming rows, or SUBTOTAL when filtered-out rows should not contribute.
| Use case | Formula or setting | What it does |
|---|---|---|
| Sum a fixed range | =SUM(B2:B6) | Adds only B2 through B6, making the included rows easy to audit. |
| Sum a growing column | =SUM(B2:B) | Adds every numeric cell from B2 downward. Store the formula outside column B to prevent a circular reference. |
| Sum an entire column | =SUM(B:B) | Adds all numeric values in column B and ignores its text header, but the formula must live in another column. |
| Sum multiple separate ranges | =SUM(B2:B6,D2:D6) | Adds two non-adjacent column ranges in one formula. |
| Sum only visible filtered rows | =SUBTOTAL(109,B2:B100) | Uses function code 109 so manually hidden and filtered-out rows are excluded from the total. |
Ask SheetsX to sum a column and verify the result
A precise Agent prompt states the source range, destination cell, expected total, formatting change, and values that must remain unchanged so the edit can be verified.
Install SheetsX- Open a new English Agent chat for a clean task context.
- Name the exact numeric range and result cell.
- Provide the required SUM formula and expected total.
- Identify every header, label, and source value that must stay unchanged.
- Review the formula bar, displayed total, and Agent verification.
Google Sheets column-sum questions
How do I sum a column in Google Sheets?+
Enter =SUM(B2:B6) in an empty cell and press Enter, replacing B2:B6 with the cells you want to add.
How do I sum an entire column in Google Sheets?+
Use =SUM(B:B) in a cell outside column B. If the formula is inside the column it sums, Google Sheets reports a circular dependency.
How can a SUM formula include new rows automatically?+
Use an open-ended range such as =SUM(B2:B) and store the formula in another column or summary area outside B2:B.
Does SUM ignore blank cells and text?+
Yes. SUM adds numeric values in the range and ignores blank cells and ordinary text, including a text header.
How do I sum only visible rows after filtering?+
Use =SUBTOTAL(109,B2:B100). Function code 109 sums visible cells while excluding filtered-out and manually hidden rows.
Why does my column total show a circular-reference error?+
The formula likely includes its own cell, such as =SUM(B:B) entered in column B. Move the formula outside column B or use a bounded range that ends above the result cell.