For IndividualsFor Analysts & FinanceFor Students & LearnersFor Teams & Automation
MENU
=FUNCTIONS

SUM()

Math & Trig

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)
→48,210

Adds every value in the range.

=SUM(B2:B20, D2:D20)
→91,455

Sums two separate ranges in one formula.

=SUM(Sales[Amount])
→184,220

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 COUNT against COUNTA to 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.00 while not being exactly zero. Compare with ROUND(x,2)=0 rather than x=0 in a control check.

Common errors

What goes wrong with SUM, and why
You seeBecauseFix
#REF!A cell in the range was deleted.Rebuild the range; a Table reference would not have broken.
Total is too lowSome 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.
Tips / Tricks / Hacks

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 tricks and formula patterns
SUM patternSUM formulaBest useWatch 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!.
Common SUM mistakes and how to fix them
SUM mistakeWhat happens in SUMFix
Double-counting section subtotalsSomeone 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 ignoredImported 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

  1. 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.
  2. Write a running cumulative balance using =SUM($C$2:C2) and copy it down 10 rows.

Related functions