SUM()
Adds up a range of numbers or a list of values.
SUM(number1, [number2], ...)SUM adds every number in the cells, ranges, or values you give it, ignoring text and blank cells. It's the most basic aggregation function in Excel.
For a sum with a condition, use SUMIF or SUMIFS instead of wrapping SUM in an IF.
Two things make a SUM wrong rather than merely inelegant, and neither raises an error. The first is a fixed range that stops short of the data: B2:B500 on a table that reached row 540 last month quietly omits forty rows. A structured reference over an Excel Table, or a whole-column reference, removes the problem. The second is numbers stored as text, which SUM ignores entirely — if a total looks too low, compare COUNT against COUNTA on the same range before looking anywhere else.
Arguments
- number1
- A number, cell reference, or range to add.
- number2, ...optional
- Additional numbers, cells, or ranges to include.
Examples
=SUM(B2:B20)Adds every value in the range.
=SUM(B2:B20, D2:D20)Sums two separate ranges in one formula.
=SUM(Sales[Amount])A structured reference over an Excel Table grows with the data, so next period's rows are included without editing the formula.
Notes and gotchas
- SUM silently ignores text, logical values, and empty cells. A column half-imported as text sums low with no error at all — compare
COUNTagainstCOUNTAto detect it. - SUM does not respect filters. A filtered list still totals every row, which is how a filtered screenshot ends up in a deck with the wrong headline number. Use
SUBTOTAL(109, range)or an Excel Table's total row instead. - Summing a column that already contains a subtotal row double-counts it. Keep totals outside the data range.
- Whole-column references like
SUM(A:A)work but make Excel consider a million rows. Table references are bounded by the data. - Floating-point arithmetic means a total can show as
0.00while not being exactly zero. Compare withROUND(x,2)=0rather thanx=0in a control check.
Common errors
| You see | Because | Fix |
|---|---|---|
| #REF! | A cell in the range was deleted. | Rebuild the range; a Table reference would not have broken. |
| Total is too low | Some values are text — imported with a currency symbol, a thousands separator, or a trailing space. | Check =COUNT(range) against =COUNTA(range); a difference means text is present. |
SUM tricks, tips, and workbook hacks
SUM is the workhorse of every financial statement and operational tracker, yet subtle spreadsheet bugs still creep into SUM ranges every day: numbers imported as text get skipped silently, newly appended rows below a static range get left out, and manually sub-totaled rows inside a single column get double-counted in the grand total.
In structured reports, pairing SUM with Excel Tables, dynamic spill references (#), or SUBTOTAL(9, ...) prevents almost every reconciliation headache.
Quick SUM checks before trusting the result
- Press Alt + = at the bottom of a column or right of a row to insert SUM automatically and see the highlighted range boundary.
- Reference a dynamic array spill with =SUM(D2#) so the total expands and contracts automatically with FILTER or UNIQUE.
- Use SUBTOTAL(9, range) instead of SUM for section subtotals—a grand-total SUBTOTAL(9, ...) at the bottom automatically ignores other SUBTOTAL cells in the range!
- If SUM is lower than expected, select the range and check the status bar: if Count exceeds Numerical Count, some numbers are stored as text.
| SUM pattern | SUM formula | Best use | Watch out |
|---|---|---|---|
| 3D sum across identical monthly tabs | =SUM(Jan:Dec!C15) | Consolidating cell C15 across all worksheet tabs positioned physically between 'Jan' and 'Dec'. | Dragging a tab outside the Jan–Dec sandwich immediately removes it from the total. |
| Sum a dynamic spill range | =SUM(F2#) | Totaling the output of a FILTER() or multiplication spill anchored in F2. | The # operator automatically tracks the exact size of the spilled array. |
| Running cumulative total down a column | =SUM($B$2:B2) | Year-to-date cash flow, cumulative inventory receipts, or Pareto running totals. | Anchor the first cell ($B$2) and leave the second cell relative (B2). |
| Sum the top 5 values in a range | =SUM(LARGE(B2:B100,{1,2,3,4,5})) | Measuring concentration risk across your top 5 clients or highest-cost projects. | Requires at least 5 numeric values in B2:B100 or LARGE returns #NUM!. |
| SUM mistake | What happens in SUM | Fix |
|---|---|---|
| Double-counting section subtotals | Someone runs =SUM(B2:B50) over a column that already contains Q1, Q2, Q3, and Q4 subtotal rows, doubling the true total. | Build both the quarterly subtotals and the annual grand total with =SUBTOTAL(9, ...), which automatically skips nested SUBTOTALs. |
| Text-formatted numbers silently ignored | Imported CSV amounts look like numbers in the cells, but SUM treats them as 0. | Convert the column to real numbers via Data → Text to Columns → Finish or multiply by 1. |
SUM practice drills
- Build a column with two section totals using =SUBTOTAL(9, ...) and a grand total at the bottom using =SUBTOTAL(9, B2:B20) to verify no double-counting occurs.
- Write a running cumulative balance using =SUM($C$2:C2) and copy it down 10 rows.




