SUM

How SUM works

SUM accepts up to 255 arguments, each of which can be a single number, a cell reference, or a range. =SUM(B2:B13) totals 12 cells. =SUM(B2:B13, F2:F13) totals 24 cells across two non-contiguous ranges. The arguments don't have to be adjacent — useful when totals come from quarterly columns or split sections.

Text values in the range are silently skipped, so a column header like "Total" in B1 doesn't break the formula. Dates are stored as serial numbers internally, so they sum (though that's rarely what you want). Empty cells count as zero. Errors propagate: any #N/A or #DIV/0! in the range cascades into the result, so wrap the offending cell in IFERROR if you want SUM to ignore it.

The two patterns worth knowing beyond "sum a column": running cumulative totals using $-anchored start cells (=SUM($B$2:B2) dragged down expands one cell at a time), and 3D references across sheets (=SUM(Jan:Dec!B5) sums B5 from every sheet between Jan and Dec). Both turn SUM from a basic adder into a structural pattern.

= SUM(number1, [number2], ...)

Arguments

SUM basic: year-to-date total from monthly column

The first SUM most people ever write. Eight months of sales in column B, the year-to-date total in one cell. One range, one cell, one number.

Two equivalent ways to write it: =SUM(B2:B9) explicitly lists the range, or =SUM(B:B) tells Excel to sum every numeric value in column B (the header text is ignored). Range version is faster and clearer; whole-column is handy when the data length keeps changing.

=SUM(B2:B9)

• •B2:B9 -> the eight monthly sales cells (blue)
• •Text ignored -> the "Month" header in B1 doesn't break SUM
• •Empty cells = 0 -> missing months don't show up as #N/A; they just add nothing
• •Result -> $448,700 - the year-to-date total
• •Alternative -> =SUM(B:B) totals the whole column. Slightly slower; auto-expands as you add rows

SUM disjoint ranges: Q1 + Q3 + Q4 only

Commission is paid in Q1, Q3, and Q4 — Q2 is a holdback for the annual close. Each quarter lives in its own column block, so the annual commission isn't a clean B2:M2 range.

SUM takes up to 255 separate arguments. List the three quarter ranges with commas between them and Excel adds them all into one total. No SUMIFS needed, no helper column — just SUM treating each argument as a sub-range.

=SUM(B2:D2, H2:J2, K2:M2)

• •B2:D2 -> Q1 months — Jan/Feb/Mar (blue)
• •H2:J2 -> Q3 months — Jul/Aug/Sep (purple)
• •K2:M2 -> Q4 months — Oct/Nov/Dec (orange)
• •Q2 deliberately skipped -> the formula tells the story: only paid-out quarters
• •Per-rep formula -> write in N2 once, drag down through N5 — each rep gets their own commission total
• •Result for Sarah -> $513K - Q1 $161K + Q3 $177K + Q4 $175K

Running balance: =SUM($B$2:B2) dragged down

Every cash log, every revenue ramp, every project burn-down needs a running total column. The trick: anchor the START of the range with $ so it never moves, leave the END unanchored so it grows by one cell as you drag down.

C2 sums B2 only ($12,400). C3 sums B2:B3 ($12,400 + $8,900). C4 sums B2:B4 ($12,400 + $8,900 + $14,250). Each row's running total is the previous row's total plus this row's amount — no recursion, no helper column.

=SUM($B$2:B2)

• •$B$2 -> start cell, fully anchored - never moves on drag-down
• •B2 -> end cell, NO anchor - grows to B3, B4, B5 as the formula moves
• •Result row by row -> C2 = $12,400. C3 = $21,300. C4 = $35,550. C5 = $51,200...
• •Per-row formula -> write =SUM($B$2:B2) in C2 once, then drag down through C9
• •Without the $ -> the range moves entirely, defeating the cumulative pattern

Sum-of-sums: grand total from row subtotals

A P&L block with five expense categories and four quarters. Each row already has its annual subtotal computed (F column). The grand total is just the SUM of the subtotal column.

This is the cleanest pattern for any cross-tab report: build subtotals in their own column, then sum those subtotals for the grand total. No double-counting, no need to sum the whole grid manually, and you can audit each row independently.

=SUM(F2:F6)

• •F2:F6 -> the row subtotals - one per expense category
• •Subtotals first -> each F cell already does =SUM(B:E) for its row
• •Grand total -> SUM of the subtotals - mathematically equivalent to summing the whole grid
• •Result -> $1,840,000 - total operating expenses for the year
• •Why this pattern -> you can drill into any row's subtotal independently. Helps audits.

Where people go wrong

1. 01
   Errors anywhere in the range break the total

   SUM happily skips text and empty cells, but an #N/A or #DIV/0! in the range cascades into the result. One broken lookup mid-column turns the annual total into #N/A and the dashboard breaks.

   Fix: Either fix the source error or wrap the offending cells with IFERROR before SUM sees them: write =IFERROR(B2,0) in the source cell, or use =SUMIF(B2:B13,">=0") which sidesteps errors entirely.
2. 02
   Hidden rows still get summed

   Filtering a table to show only one region doesn't change what SUM totals. SUM ignores nothing — every row in the range, hidden or not, gets added. The visible total on screen doesn't match the formula's answer.

   Fix: Use SUBTOTAL (with a function number of 9) or AGGREGATE if you want the total to respond to filters. =SUBTOTAL(9, B2:B100) sums only visible rows. Useful in any filtered table.
3. 03
   Confusing whole-column refs with auto-expand

   =SUM(B:B) totals every numeric cell in column B forever. If you later add a stray number lower in the column (a memo, a junk paste, anything), it silently joins the total. Hard to spot.

   Fix: Prefer an explicit range (B2:B13) for fixed datasets, or convert the data to an Excel Table and use structured references (=SUM(Table1[Sales])). Tables auto-expand cleanly and reject stray rows below.
4. 04
   SUM of dates returns a giant number

   Excel stores dates as serial numbers (Jan 1 1900 = 1, today is ~46000). =SUM of a date column returns the sum of those serial numbers — a meaningless six-digit value that looks like a bug.

   Fix: There's almost never a reason to SUM a date column. If you meant to count rows, use COUNTA. If you meant earliest or latest, use MIN/MAX. If you needed an elapsed total, subtract two dates instead.

Notes

• SUM is the single most-used function in Excel. The Σ AutoSum button on the Home tab inserts a SUM with a guessed range.
• Accepts up to 255 arguments. Each can be a number, a cell, a range, or a name.
• Text values are silently skipped. Headers don't break the formula.
• Empty cells are treated as zero.
• Errors (#N/A, #DIV/0!, etc.) propagate. Wrap offending cells with IFERROR to suppress.
• Whole-column refs work (=SUM(B:B)) but are slower and pick up stray values.
• Excel Tables give you structured references: =SUM(Table1[Sales]). Cleanest pattern when the data grows.
• 3D references work across sheets: =SUM(Jan:Dec!B5) sums B5 from every sheet between Jan and Dec inclusive.
• For conditional sums (one criterion), use SUMIF. For multi-criteria, use SUMIFS. For dynamic arrays, use SUMPRODUCT.
• SUBTOTAL(9, range) sums only visible rows — useful in filtered tables. AGGREGATE has the same behaviour with more options.

What to learn next

Now prove it