SUM Function in Excel and Google Sheets
Purpose
Add all numeric values in one or more ranges and return the total
Return value
The sum of all numeric values in the supplied arguments; blank cells and text are ignored
SUM is the function that starts every spreadsheet education and never stops being useful. It is the most used function in Excel and Google Sheets by an enormous margin, and yet there are variations and edge cases that trip people up even after years of experience. The difference between a finite range and a whole-column reference matters when data grows. Non-adjacent ranges can be summed in a single formula without chaining multiple SUM calls with + signs. And the silent failure mode where SUM returns zero because numbers are stored as text — not formatting, actual text — has caused me to audit broken financial reports more times than I would like to admit. Getting SUM fully right, including knowing when and why it behaves unexpectedly, is genuinely foundational. Everything from SUMIF to SUMPRODUCT builds on the same core logic.
Syntax
✓ Excel 2003+
✓ Google Sheets
= Same syntax
=SUM(number1, [number2], ...)
Arguments
| Argument | Required | Description |
|---|---|---|
| number1 | Required | The first range, cell reference, or literal number to include. Most commonly a range like A2:A100 or a whole-column reference like A:A. |
| number2 ... | Optional | Additional ranges, cells, or numbers, up to 255 arguments total. Each is added to the running total. Use commas to separate arguments: =SUM(A2:A10,C2:C10,E5). |
How it works
SUM evaluates each argument, extracts all numeric values, and returns the total. Blank cells and cells containing text are skipped silently — they contribute zero without causing an error. Numbers formatted as currency, percentages, or dates are numeric values and are included. A cell containing the text string 100 (stored as text rather than a number) is skipped, which is the most common source of unexpectedly low totals. A whole-column reference like A:A sums every numeric cell in the column including any added in future rows, at a small performance cost compared to a finite range. SUM accepts up to 255 arguments which can mix ranges and individual cells freely. Error values in the range such as #N/A or #DIV/0! cause SUM to return an error — wrap the error-producing cells in IFERROR, or use AGGREGATE(9,6,range) to sum while ignoring errors. For conditional totals where only certain rows should be included, SUMIF adds a single-condition criterion and SUMIFS adds multiple conditions.
Examples
1
Total the monthly sales figures from a column of daily amounts.
| 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 sits in the Total row below the data. If new rows are inserted between the data and the Total row, extend the range manually or switch to =SUM(B:B) to include all current and future rows automatically.
2
Sum two non-adjacent columns — January and March — in a single formula, skipping the February column between them.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Week | Jan | Feb | Mar | Jan+Mar |
| 2 | 1 | 1200 | 900 | 1350 | =SUM(B2:B5,D2:D5) |
| 3 | 2 | 850 | 780 | 1100 | 3300 |
| 4 | 3 | 1450 | 1020 | 1280 | 2730 |
| 5 | 4 | 970 | 860 | 990 | 1960 |
| 6 | Total | 4470 | 3560 | 4720 | 9190 |
Row 2: 9190
— Jan total 4470 plus Mar total 4720 equals 9190. The Feb column is excluded because D2:D5 is not included — only B2:B5 and D2:D5 are passed as arguments.
Comma-separated arguments in SUM make multi-range totals clean and readable. The alternative — =SUM(B2:B5)+SUM(D2:D5) — works too but is wordier and harder to extend when a third non-adjacent range is added.
3
Use a whole-column reference so new rows added below the current data are included in the total automatically.
| 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) gives 11,160 — identical to the finite range. Any new rows added below row 11 are automatically included without updating the formula.
Column headers are text and are ignored by SUM, so using B:B is safe even when B1 contains a label. The slight performance cost of a whole-column reference is negligible on any typical business-sized dataset.
Common use cases
1. Total a column of sales, expenses, or quantities for a period summary
=SUM(B2:B31)
2. Sum a whole column so future rows are included automatically without updating the formula
=SUM(B:B)
3. Add two or more non-adjacent ranges in a single formula
=SUM(B2:B100,D2:D100)
4. Sum only the visible rows of a filtered table using SUBTOTAL instead of SUM
=SUBTOTAL(9,B2:B100)
5. Total a range while ignoring any error values using AGGREGATE
=AGGREGATE(9,6,B2:B100)
Common errors
SUM returns zero or understated total
Numbers in the range are stored as text — imported data often arrives this way. SUM ignores text cells entirely.
Fix: Select the column, look for the green triangle warning, and click Convert to Number. Or paste-special-multiply by 1 to force conversion. Check individual cells with =ISNUMBER(B2).
SUM returns an error when any cell contains #N/A or #DIV/0!
SUM propagates errors from the range — one error cell makes the whole SUM error.
Fix: Wrap error-producing formulas in IFERROR to replace with 0, or use =AGGREGATE(9,6,B2:B100) which ignores error values automatically.
SUM includes values you expected to be excluded
The range is wider than intended, or a whole-column reference B:B picks up data in rows you did not expect to have numbers.
Fix: Use a finite range with explicit row numbers, or audit the column for unexpected numeric values outside the data area.
Tips and variations
Use SUBTOTAL for filter-aware totals
=SUM always includes all rows, even hidden ones in a filtered table. =SUBTOTAL(9,B2:B100) sums only visible rows. Switch to SUBTOTAL whenever your data lives in a filtered table — it is the correct pattern and I use it as my default in any table that might be filtered.
=SUBTOTAL(9,B2:B100)
Diagnose text-as-number problems with ISNUMBER
Before concluding SUM is wrong, check a suspicious cell with =ISNUMBER(B2). FALSE means the value is stored as text and SUM is ignoring it. Converting those cells to real numbers fixes the total immediately.
=ISNUMBER(B2)
Extend to conditional totals with SUMIF
Once SUM is solid, SUMIF adds a single condition — sum only the North region rows, only the rows above a threshold, only the rows in a specific month. The syntax extends naturally from SUM and the two functions together cover most real-world totalling requirements.
=SUMIF(C2:C100,"North",B2:B100)
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, multi-argument syntax, and the treatment of text and blank 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
SUM adds all the numeric values in a range and returns the total. It ignores blank cells and cells containing text, so mixed-content columns are handled safely. SUM is the most widely used function in Excel and Google Sheets and is available in every version of both applications.
Both produce the same result for small ranges, but SUM is more practical for large ones. =A2+A3+A4+A5 works but becomes unwieldy at scale. =SUM(A2:A100) handles 99 cells in the same length. SUM also handles blank cells gracefully — the + operator would require you to know no cell is blank. SUM is always the preferred approach for summing a range.
The numbers are stored as text rather than real numeric values — a common result of importing data from CSV files, web pages, or external systems. SUM ignores text cells and returns zero or an understated total. Fix it by selecting the column and using Data > Text to Columns, or by multiplying the column by 1 using Paste Special > Multiply to force conversion to real numbers.
Yes. SUM accepts multiple arguments separated by commas: =SUM(A2:A10,C2:C10,E5) adds two ranges and one individual cell in a single formula. You can include up to 255 arguments — ranges, individual cells, and literal numbers — all summed together.
SUM totals every numeric cell in a range unconditionally. SUMIF totals only the cells that meet a condition — for example, only rows where the region column equals North. Use SUM for an unconditional total and SUMIF when the sum depends on a criterion in another column.