SUM a Column in Excel and Google Sheets
Purpose
SUM is the first formula most people learn and somehow remains one of the most used formulas in any spreadsheet.…
SUM is the first formula most people learn and somehow remains one of the most used formulas in any spreadsheet. But there are a few variations worth knowing that go beyond the obvious =SUM(A2:A10). The difference between summing a fixed range and summing an entire column matters when data grows. Summing non-contiguous ranges in a single formula saves having to chain multiple SUMs together. And understanding why SUM sometimes returns zero when there are clearly numbers in the column — because they are stored as text — is the diagnostic knowledge that saves an embarrassing amount of time. I have seen spreadsheets shared with clients where the total column was wrong for months because of text-as-number data, and SUM silently returned the wrong answer without any error. For conditional totals, SUMIF and SUMIFS extend SUM with criteria — but the foundation is always getting the basic SUM right first. Getting SUM right, including knowing its edge cases, is genuinely important for anyone who works with data regularly.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=SUM(range)
Arguments
| Argument | Required | Description |
|---|---|---|
| number1 | Required | The first range, cell, or value to include. Most commonly a range like A2:A100 or a whole column reference like A:A. |
| number2 ... | Optional | Additional ranges, cells, or values, up to 255 total. Useful for summing non-contiguous ranges: =SUM(A2:A10,C2:C10). |
How it works
SUM adds all numeric values in the specified range. Blank cells and cells containing text are ignored — they contribute zero to the total without causing an error. A cell formatted as a number but storing a text string is also ignored, which is the most common cause of unexpectedly low SUM totals. Whole-column references like A:A cover all rows in the column including any added in the future, but at a small performance cost compared to a finite range. A column header is text and therefore ignored by SUM, so you can safely use A:A even when A1 contains the word Sales. SUM accepts up to 255 arguments which can be ranges, individual cells, or literal numbers, all separated by commas. Error values like #N/A or #DIV/0! in the range cause SUM to return an error — wrap the error-producing cells in IFERROR or use AGGREGATE with the ignore-errors option to work around this.
Examples
1
Sum the total sales for the month from a column of daily sales figures.
| A | B | |
|---|---|---|
| 1 | Day | Sales |
| 2 | 1 | 1200 |
| 3 | 2 | 850 |
| 4 | 3 | 1450 |
| 5 | 4 | 970 |
| 6 | 5 | 1100 |
| 7 | 6 | 1320 |
| 8 | 7 | 890 |
| 9 | 8 | 1560 |
| 10 | 9 | 740 |
| 11 | 10 | 1080 |
| 12 | Total | =SUM(B2:B11) |
Row 2: 11160
— The ten daily sales figures add up to 11,160.
The formula goes in the Total row below the data. B2:B11 covers the ten sales cells. If new rows are added between the data and the Total row, extend the range or use =SUM(B:B) to include all current and future rows automatically.
2
Sum the entire column including any rows added in the future by using a whole-column reference.
| A | B | |
|---|---|---|
| 1 | Sales | Total |
| 2 | 1200 | =SUM(B:B) |
| 3 | 850 | 11160 |
| 4 | 1450 | |
| 5 | 970 | |
| 6 | 1100 | |
| 7 | 1320 | |
| 8 | 890 | |
| 9 | 1560 | |
| 10 | 740 | |
| 11 | 1080 |
Row 2: 11160
— SUM(B:B) totals every numeric value in column B — the same 11,160 as the fixed range. Any new sales figures added below row 11 are automatically included.
The column header (if any) is text and is ignored by SUM. A whole-column reference is the approach I use when data is updated regularly and the row count changes — it eliminates the need to update the formula range each time.
3
Sum two non-contiguous sales columns — January and March — skipping February, in a single SUM formula.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Month | Jan | Feb | Mar | Jan+Mar Total |
| 2 | Week 1 | 1200 | 900 | 1350 | =SUM(B2:B11,D2:D11) |
| 3 | Week 2 | 850 | 780 | 1100 | |
| 4 | Week 3 | 1450 | 1020 | 1280 | |
| 5 | Week 4 | 970 | 860 | 990 | |
| 6 | Total | 4470 | 3560 | 4720 | 9190 |
Row 2: 9190
— January total 4470 plus March total 4720 equals 9190. February is excluded because its column D2:D11 is not included in the SUM arguments.
Multiple ranges in one SUM formula are separated by commas. You can include as many non-contiguous ranges as needed — SUM handles them all in a single calculation without chaining separate formulas with + signs.
Common use cases
1. Total a column of sales figures for a daily, weekly, or monthly summary
=SUM(B2:B31)
2. Sum an entire column so future rows added below are included automatically
=SUM(B:B)
3. Add two non-adjacent columns together in one formula to skip an intervening column
=SUM(B2:B100,D2:D100)
4. Sum only visible rows in a filtered table using SUBTOTAL instead of SUM
=SUBTOTAL(9,B2:B100)
5. Total a column while ignoring error values using AGGREGATE
=AGGREGATE(9,6,B2:B100)
Common errors
SUM returns zero or a lower total than expected
Some numbers in the range are stored as text — they look like numbers but SUM ignores them. This happens when data is imported from CSV or copied from websites.
Fix: Select the affected cells, look for a green triangle warning in the corner, and click Convert to Number. Or multiply the column by 1 using Paste Special > Multiply to force conversion.
SUM returns an error when the range contains #N/A or #DIV/0!
SUM propagates errors — if any cell in the range contains an error, the SUM result is also an error.
Fix: Wrap error-producing formulas in IFERROR to replace errors with 0, or use AGGREGATE(9,6,range) which ignores error values automatically.
SUM includes values from the header row
The header is a text value and SUM ignores text, so this should not cause a problem. If a header happens to contain a number, SUM will include it.
Fix: Put the column header in a row above the SUM range so it is outside the summed area, or use a text-based header that SUM naturally ignores.
Tips and variations
Use whole-column references for growing datasets
=SUM(B:B) automatically includes any rows added below the current data. For a table that is updated regularly, this eliminates the ongoing maintenance of extending the range. The small performance cost is negligible on any typical business dataset.
=SUM(B:B)
Use SUBTOTAL to sum only filtered rows
When a table is filtered, =SUM still totals all rows including hidden ones. =SUBTOTAL(9,B2:B100) sums only the visible rows, making it the correct function for filter-aware totals in any dashboard or report.
=SUBTOTAL(9,B2:B100)
Diagnose text-as-number problems with ISNUMBER
Before assuming your data is correct, check a cell with =ISNUMBER(B2). If it returns FALSE for a cell that looks like a number, the value is stored as text and SUM is ignoring it. Converting those cells to real numbers fixes the total immediately.
=ISNUMBER(B2)
Excel vs Google Sheets
Excel vs Google Sheets
SUM works identically in Excel 2003 and newer and every version of Google Sheets. Whole-column references, multiple range arguments, and the treatment of text cells are the same in both applications. SUBTOTAL and AGGREGATE are also available in both, though AGGREGATE was introduced in Excel 2010.
Frequently asked questions
Use =SUM(A:A) to sum all numeric values in column A, or =SUM(A2:A100) to sum a specific range. SUM ignores text and blank cells automatically. Using a whole column reference like A:A is convenient when new rows are added regularly because the formula includes them without being updated.
SUM(A:A) sums the entire column including any future rows added below the current data. SUM(A2:A100) sums only the specified range. Using a whole-column reference is convenient but has a slight performance cost on very large sheets. If the column has a header, SUM ignores it automatically because text is not numeric.
The numbers are stored as text rather than real numbers. This happens when data is imported from a CSV, copied from a website, or prefixed with an apostrophe. SUM ignores text cells and returns zero or an incorrect total. Fix it by selecting the column, using Data > Text to Columns, or multiplying by 1 to convert text-numbers to real numbers.
Yes. SUM accepts multiple separate ranges separated by commas: =SUM(A2:A10,C2:C10,E2:E10) sums three non-contiguous ranges in one formula. You can also include individual cells: =SUM(A2:A10,B5,C2:C10).
SUM already ignores blank cells and text automatically. For cells containing errors like #N/A or #DIV/0!, wrap each error-producing cell in IFERROR first, or use AGGREGATE: =AGGREGATE(9,6,A2:A100) sums while ignoring error values. The 9 is the SUM function code and 6 tells AGGREGATE to ignore errors.