Math & Trig

SUM Function in Excel

Adds all the numbers in a range of cells.

Syntax

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

Arguments

  • number1 (required): The first number or range to add
  • number2 (optional): Additional numbers or ranges

Examples

  • =SUM(A1:A10) - Sum all values in range A1 to A10 - Result: 550
  • =SUM(A1:A10, C1:C10) - Sum multiple ranges - Result: 1100
  • =SUM(5, 10, 15) - Sum individual numbers - Result: 30

Excel SUM function documentation

  • SUM is a Math & Trig function that adds all numeric values in a range or list of arguments.
  • Syntax: =SUM(number1, [number2], …) or =SUM(A2:A100). Available in all Excel versions.
  • SUM ignores text, blank cells, and logical values unless they are stored as numbers.
  • Related: [SUMIF](/functions/sumif/), [SUMIFS](/functions/sumifs/), [SUBTOTAL](/functions/subtotal/), [AGGREGATE](/functions/aggregate/).

SUM syntax and range best practices

  • Use Excel Tables (Ctrl+T) so =SUM(Table[Amount]) expands when rows are added.
  • Avoid full-column references =SUM(A:A) on huge sheets — bound the range to your data block.
  • 3D reference across sheets: =SUM(Jan:Dec!B10) totals B10 on every sheet from Jan through Dec.
  • AutoSum shortcut Alt+= inserts SUM on the selected cell row or column.

Step-by-step: build a reliable total row

  • Step 1 — Convert raw data to an Excel Table with headers (Amount, Region, Date).
  • Step 2 — In the total row: =SUM(Table[Amount]) or =SUBTOTAL(109,Table[Amount]) if users filter.
  • Step 3 — Spot-check three rows manually against calculator or status bar selection.
  • Step 4 — If totals look low, scan for text numbers (green triangle) and use VALUE or multiply by 1.
  • Step 5 — For conditional totals use [SUMIF](/functions/sumif/) or [SUMIFS](/functions/sumifs/) instead of SUM alone.

SUM vs SUBTOTAL vs SUMIFS

  • SUM — all numeric cells in the range, including hidden rows unless manually excluded.
  • SUBTOTAL — respects filters and outline levels; use 109 for SUM ignoring hidden rows.
  • SUMIFS — conditional total when you need region, date, or status filters in one formula.
  • Compare: [SUMIF vs SUMIFS](/compare/sumif-vs-sumifs/) · Problem: [SUMIF not working](/problems/sumif-not-working/).

Worked examples to copy

  • Row total: =SUM(B2:D2) copied down a monthly sales sheet.
  • Running structure: put =SUM($B$2:B2) in C2 and fill down for cumulative totals.
  • Multi-sheet: =SUM('North'!D5, 'South'!D5, 'East'!D5) or =SUM(North:East!D5).
  • With IF for blanks: =SUMIF(A:A, "<>" , B:B) is wrong pattern — use SUM on numeric column only.

People also ask

  • Why does SUM return 0? — No numeric values in the range, or numbers stored as text.
  • Does SUM include hidden rows? — Yes. Use SUBTOTAL(109, range) for filtered reports.
  • Can SUM add text numbers? — Only if Excel recognizes them as numbers, not plain text labels.
  • SUM vs plus operator? — =A1+A2+A3 works for few cells; SUM scales to whole ranges.

Common errors

  • #VALUE! when text is in range
  • Returns 0 for empty cells

Use cases

  • Budget totals
  • Sales calculations
  • Inventory counts

Frequently asked questions

  • What is the SUM function in Excel? SUM is Excel's most basic and widely used function that adds numbers together. The syntax is =SUM(number1, [number2], ...) where you can include individual numbers, cell references, or ranges. For example, =SUM(A1:A10) adds all values from A1 to A10, and =SUM(A1, B1, 100) adds the values in A1, B1, and the number 100.
  • What is the difference between SUM and SUMIF? SUM adds all numbers in a range unconditionally, while SUMIF adds only numbers that meet a specific criterion. For example, =SUM(B:B) adds everything in column B, but =SUMIF(A:A, "Sales", B:B) only adds values in column B where the corresponding cell in column A equals "Sales". Use SUMIFS for multiple conditions.
  • Why is SUM returning 0 or wrong result? SUM may return 0 or incorrect results because: 1) Numbers are stored as text - select cells, click the warning icon, and convert to numbers; 2) Cells contain hidden characters or spaces; 3) The range reference is wrong; 4) Cells are formatted as text before entering numbers. To fix text-numbers, use =SUM(VALUE(A1), VALUE(A2)) or multiply by 1.
  • Can SUM add cells from multiple sheets? Yes, SUM can add cells across multiple sheets using 3D references. Use =SUM(Sheet1:Sheet12!A1) to sum cell A1 from Sheet1 through Sheet12. You can also reference specific sheets: =SUM(Sheet1!A1, Sheet2!A1, Sheet3!A1). This is useful for consolidating monthly or departmental data.
  • How do I SUM only visible cells in Excel? To sum only visible cells (ignoring hidden/filtered rows), use SUBTOTAL instead of SUM. The syntax is =SUBTOTAL(109, A1:A100) where 109 means SUM ignoring hidden values. Use 9 instead of 109 to include manually hidden rows but exclude filtered rows. AGGREGATE function offers even more options for ignoring errors and hidden rows.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use SUM

  • Budget totals — common Math & Trig scenario for SUM.
  • Sales calculations — common Math & Trig scenario for SUM.
  • Inventory counts — common Math & Trig scenario for SUM.

SUM in the Math & Trig category

  • Browse all Math & Trig functions at /categories/math-trig/ for related formulas.
  • SUM syntax: =SUM(number1, [number2], ...)
  • number1 (required): The first number or range to add
  • number2 (optional): Additional numbers or ranges
  • Confirm SUM arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm SUM arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • SUMIF (/functions/sumif/): Adds the cells specified by a given condition or criteria.
  • SUMIFS (/functions/sumifs/): Adds cells that meet multiple criteria across multiple ranges.
  • AVERAGE (/functions/average/): Returns the average (arithmetic mean) of the arguments.
  • COUNT (/functions/count/): Counts the number of cells that contain numbers.

Common problems and fixes

  • Why Does Excel Show the Formula Instead of the Result? (/problems/excel-shows-formula-not-result/): Turn off Show Formulas, set the cell to General format, delete any leading space before the equals sign, and re-enter the formula.

Errors to watch for

  • #VALUE! when text is in range — review causes on linked error pages in the directory.
  • Returns 0 for empty cells — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter SUM on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

SUM worked examples to copy

  • =SUM(A1:A10) — Sum all values in range A1 to A10. Expected result: 550.
  • =SUM(A1:A10, C1:C10) — Sum multiple ranges. Expected result: 1100.
  • =SUM(5, 10, 15) — Sum individual numbers. Expected result: 30.

SUM reference summary for crawlers and offline review

  • SUM belongs to the Math & Trig category in Excel. Adds all the numbers in a range of cells.
  • Full syntax: =SUM(number1, [number2], ...). Open /functions/sum/ for parameters, FAQs, and related pages.
  • Common mistakes: #VALUE! when text is in range; Returns 0 for empty cells
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.