Skip to main content
Excel Cookbook
ExcelSUMbasic functionsarithmetictotalsdata analysis

The Problem: Adding Numbers Shouldn't Be Hard

You've got a column of 500 sales figures and need the total. You could click each cell and add them with + signs... but you'd be there until next Tuesday. Even selecting a range and looking at the status bar feels clunky when you need the total in a specific cell for your report.

SUM is the most-used function in Excel for a reason — it's fast, flexible, and essential for any spreadsheet work.

The Ingredients: Understanding SUM's Setup

SUM adds up numbers. That's it. But it's more versatile than you might think.

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

Parameter Description
number1 The first number, cell reference, or range to add.
[number2], ... Optional. Additional numbers or ranges (up to 255 arguments).

The Recipe (Step-by-Step): Totaling Sales

Scenario: Sum monthly sales in cells B2:B13.

Month Sales
January $1,200
February $980
March $1,450
... ...
  1. Click cell B14 (where you want the total).
  2. Type: =SUM(B2:B13)
  3. Press Enter. Done! The total appears instantly.

Multiple Ranges

Sum sales from multiple sheets or non-adjacent ranges:

=SUM(B2:B13, D2:D13, F2:F13)

Or across sheets:

=SUM(Jan!B2:B13, Feb!B2:B13, Mar!B2:B13)

Advanced Recipes

SUM with Conditions (Use SUMIF)

To sum only "North" region sales:

=SUMIF(A2:A100, "North", C2:C100)

Running Total

Create a cumulative sum:

=SUM($B$2:B2)

Copy this down the column. Each row adds the current value to all previous values.

Ignoring Errors

If your range might contain #N/A or other errors:

=SUMPRODUCT((IFERROR(B2:B13, 0)))

Pro Tips: Sharpen Your Skills

  • AutoSum shortcut: Select a cell below your numbers and press Alt + = (Windows) or Cmd + Shift + T (Mac). Excel auto-detects the range.
  • Text that looks like numbers: SUM ignores text values. If "100" is stored as text, it won't be counted. Use VALUE() to convert first.
  • Boolean values: TRUE = 1, FALSE = 0, but only when passed directly (not in cell references). SUM(TRUE, TRUE) = 2, but SUM(A1:A2) where A1=TRUE, A2=TRUE = 0.

Troubleshooting: Common Pitfalls

  • Result is 0 when it shouldn't be: Your "numbers" might be stored as text. Check the cell format or look for the green triangle warning.
  • #VALUE! error: One of your arguments isn't a valid number or range.
  • Wrong total: Check for hidden rows or filtered data. SUM includes hidden rows! Use SUBTOTAL(109, range) to sum only visible cells.

Related Recipes