The Excel SUM function, explained interactively.
SUM adds every number in its arguments. The foundational Excel formula — every other aggregate (SUMIF, SUMIFS, SUMPRODUCT) is a SUM with extra logic wrapped around it. Silently skips blanks, text, and booleans, so pointing it at a mixed column never errors — it just ignores the non-numeric bits.
How to use SUM
- Select the cell where you want the total, then type
=SUM(. - Select the range to total — drag with the mouse or type
B2:B8. SUM accepts any number of comma- separated ranges and individual values. - Close the parenthesis and press Enter. Fast path:
Alt+=(Windows) orCmd+Shift+T(Mac) auto-inserts SUM with a best-guess range. - For conditional totals, reach for SUMIF or SUMIFS — SUM itself has no filter.
Hover the cards to see which part of the formula each argument maps to. Notice in the demo below how the blank cell (Dev) and the "N/A" text cell (Eva) contribute nothing — SUM skips them silently.
| A | B | D | |
|---|---|---|---|
| 1 | Rep | Amount | Total |
| 2 | Ava | 1,200 | 6,900 |
| 3 | Ben | 850 | |
| 4 | Chloe | 2,300 | |
| 5 | Dev | (blank) | |
| 6 | Eva | N/A | |
| 7 | Finn | 1,450 | |
| 8 | Gus | 1,100 |
=SUM(B2:B8). SUM added 5 numeric cells for a total of 6,900. The 2 non-numeric cells (1 blank + 1 text) are silently skipped — SUM never errors on mixed columns, it just ignores anything that isn't a number.SUM syntax and arguments
Variadic — one required argument, up to 254 optional more. See Microsoft’s official SUM reference for the canonical specification.
SUM examples
Four patterns that cover most real-world SUM use.
Example 1: SUM — total a column
The canonical case. Add every numeric value in a single contiguous range.
Works identically whether the range has 8 rows or 8,000. SUM handles blank cells and text labels silently, so header-adjacent ranges and mixed-content columns never throw. Excel’s AutoSum (Alt+=) produces this formula with a best-guess range.
Example 2: SUM — multiple non-adjacent ranges
Combine disjoint ranges and individual values in one call. Useful for monthly totals across sheets or aggregating fragmented regions.
Each comma-separated argument is processed and added. The literal 100 is added once — scalars mix freely with ranges. Up to 255 arguments means you can total a whole quarter in one formula without intermediate cells.
Example 3: SUM across sheets (3D reference)
One of SUM’s best-kept tricks: point at the same cell across multiple tabs with a colon range in the sheet name.
Totals cell B2 on every sheet from Jan through Dec. Requires the tabs to be in order; a sheet move breaks the reference. Clean way to roll up monthly tabs into a summary without a lookup formula.
Example 4: SUM combined with other formulas
SUM inside IF, SUMPRODUCT, or array formulas unlocks conditional totals without SUMIFS.
Totals B2:B8 only where column A equals "East". In Excel 365 this works as a dynamic array; in older Excel, enter with Ctrl+Shift+Enter. For pure conditional sums, SUMIFS is cleaner, but this idiom is worth knowing for complex tests that SUMIFS can’t express.
Common SUM errors and fixes
Four failure modes, each with what to check and how to recover.
SUM returns 0 when cells look like numbers
Cause: the “numbers” are stored as text — left-aligned in the cell, often with a green triangle in the corner. SUM skips them like any other text.
Fix: select the range, Data → Text to Columns → Finish. Or multiply by 1 in a helper column: =B2*1. Or wrap in VALUE: =SUMPRODUCT(VALUE(B2:B8)). The imported- from-CSV-and-still-text-because-of-one-non-breaking- space situation is the number-one SUM gotcha.
SUM returns #VALUE! or #N/A
Cause: one of the cells in the range contains an error value (like #DIV/0! or #N/A). Unlike blanks and text, errors propagate — SUM returns the same error.
Wrap the range with IFERROR at the cell level (=IFERROR(B2, 0) in a helper column), or use =AGGREGATE(9, 6, B2:B8), which is SUM with built-in error skipping (function 9 = SUM, option 6 = ignore errors).
SUM total is wildly off
Cause: the range extends into unintended rows — usually a hidden footer, a running total, or an old subtotal inside the range double- counting the data.
Click the SUM cell and watch the blue range outline. If it extends past your data, fix the range in the formula bar. Tables (Ctrl+T) anchor ranges semantically so added rows expand the SUM automatically — more robust than raw cell references for growing datasets.
SUM circular reference warning
Cause: the SUM range includes the cell that contains the SUM formula itself. Common when a total row sits inside its own data block.
Move the total cell outside the range, or shrink the range to exclude it. Excel’s status bar shows the circular cell; double-click to jump to it. Do not enable “iterative calculation” as a workaround — that masks the bug rather than fixing it.
SUM vs SUMIF, SUMIFS & AutoSum
SUM totals; SUMIF and SUMIFS total conditionally; AutoSum is the keyboard shortcut, not a separate function. Pick by how selective the total needs to be.
| Function | Conditional? | Args | Typical use |
|---|---|---|---|
| SUM | No | number1, [number2], … | Total a column, aggregate multiple ranges |
| SUMIF | One condition | range, criteria, [sum_range] | Total where column A matches a value |
| SUMIFS | Multiple conditions | sum_range, criteria_range1, criteria1, … | Dashboards, multi-filter reports, pivot-replacement |
| SUMPRODUCT | Any boolean expression | array1, [array2], … | Weighted sums, legacy pre-SUMIFS conditional totals |
Rule of thumb: SUM for unconditional totals, SUMIF for a single condition, SUMIFS for two or more. AVERAGEIFS, COUNTIF, and COUNTIFS follow identical argument shapes — once you know one, the family is straightforward.
SUM frequently asked questions
6.01Does SUM include text and blank cells?▸
No — SUM silently skips any cell that isn’t a number. Blanks, text strings, and booleans contribute nothing; SUM doesn’t error, it just ignores them. That makes SUM safe to point at mixed columns where some rows have "N/A" placeholders.
6.02Why does my SUM formula show 0 when the cells look like numbers?▸
Almost always because the “numbers” are text. A number stored as text left-aligns in the cell and shows a small green triangle in the corner. Select the range and use Data → Text to Columns → Finish, or multiply by 1 to force conversion. SUMPRODUCT with a VALUE() wrap also works.
6.03How do I sum with conditions in Excel?▸
Use SUMIF for one condition, SUMIFS for multiple conditions. SUMIF(range, criteria, [sum_range]) adds the cells in sum_range where range matches criteria. SUMIFS flips the argument order — sum_range first, then pairs of criteria_range and criteria. The plural form scales to up to 127 condition pairs.
6.04Can SUM add multiple ranges at once?▸
Yes — SUM takes up to 255 arguments, each can be a range or a single value. =SUM(B2:B8, D2:D8, 100) totals two columns and adds a literal 100. Ranges can be on different sheets: =SUM(Jan!B2:B8, Feb!B2:B8) sums across monthly tabs.
6.05What’s the keyboard shortcut for SUM in Excel?▸
Alt+= on Windows, Cmd+Shift+T on Mac — AutoSum. Excel inserts =SUM() with a best-guess range based on adjacent cells. Press Enter to accept, or edit the range before pressing Enter. Works from the cell below a column or to the right of a row.
Microsoft Excel is a registered trademark of Microsoft Corporation. Google Sheets is a trademark of Google LLC. Formula Gym is not affiliated with, endorsed by, or sponsored by either company.