How to sum a column in Excel
Formula (Excel and Google Sheets)
=SUM(B2:B7)Select the cell below or beside the numbers and use =SUM(B2:B7), or press Alt+= (AutoSum) to have Excel write it for you.
- Result on the example
- $2,100.80 in
E1 - Works in
- Every current version
- Google Sheets
- Same formula
See it work
The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.
How it works
SUM adds every number in the range B2:B7, the first cell to the last, and skips empty cells and text. The colon means "everything from B2 to B7", so the total updates as soon as any bill changes.
=SUM(B2:B7)Result2100.8
B2:B76 rows × 1 col: $412.36, $398.12, $356.80, $301.45, …A range: 6 cells in one column.
SUM(B2:B7)2100.8SUM: Adds numbers and ranges. Skips text and blanks.- number1
B2:B76 rows × 1 col: $412.36, $398.12, $356.80, $301.45, …
- number1
In Google Sheets
The same formula works in Google Sheets: =SUM(B2:B7). We ran it in Google Sheets mode on the same data and it returns $2,100.80.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B:B) | $2,100.80 | A whole-column reference keeps working when you add rows. Put the total outside column B, or it becomes a circular reference. |
| =B2+B3+B4+B5+B6+B7 | $2,100.80 | Fine for two or three cells, but it breaks with #VALUE! if a cell holds text, and it does not grow when you insert a row. |
Common mistakes
- A total inside the range it adds (
=SUM(B2:B8)typed in B8) is a circular reference. Excel warns and shows 0. - Numbers stored as text are skipped without a warning. If the total looks too small, look for numbers aligned to the left of the cell.
- Inserting a row just below the last number is outside
B2:B7. Insert rows inside the range, or use an Excel Table, so the reference grows.
Try it yourself
Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.
Sum a column
E1, add up the electric bills in B2:B7.Questions
What is the shortcut to sum a column?
Click the cell below the numbers and press Alt+= (Cmd+Shift+T on a Mac). Excel writes the SUM and selects the range for you to check.
How do I sum only the visible rows after filtering?
Use =SUBTOTAL(109,B2:B7). SUM always adds hidden rows too.
How do I make the total include rows I add later?
Use the whole column, =SUM(B:B), with the total outside column B, or convert the data to a Table (Ctrl+T) and use =SUM(Table1[Electric bill]), which grows with the table.
Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.