How to use the SUM function in Excel

How to use the SUM function in Excel

To add numbers in Excel, enter a SUM formula such as =SUM(A1:A5) to total a contiguous range. For scattered cells, list ranges or cells separated by commas (for example =SUM(A1:A3,C1:C3)). Below you will find clear, step-by-step instructions for common scenarios, shortcuts to speed work, and a troubleshooting section for the errors you are likely to encounter.

Basic SUM: add a column or row

The SUM function returns the arithmetic total of the numeric values in the cells you reference. Its simplest form is =SUM(range). Follow these steps to add a column or row:

1. Select the cell where you want the total to appear.
2. Type =SUM( then select a contiguous range with your mouse or type the range (for example A1:A5).
3. Type ) and press Enter. Excel calculates the total.

Worked example: suppose A1 through A5 contain 10, 20, 30, 40, 50. Entering =SUM(A1:A5) returns 150.

Use AutoSum and keyboard shortcuts

AutoSum inserts a SUM formula for a nearby column or row automatically. It is useful when you need a quick total without typing the range.

1. Select the cell below a column of numbers or to the right of a row of numbers.
2. Click the AutoSum button on the Home or Formulas tab, or press Alt+= (Windows) or Command+Shift+T (macOS Excel variations may differ).
3. Excel guesses the range; adjust it if necessary and press Enter.

For more keyboard options and a concise list of shortcuts, see the guide on AutoSum and keyboard shortcuts in Excel.

Non-contiguous ranges and multiple areas

You can add cells that are not next to each other by separating ranges or individual cells with commas inside SUM. This is practical when values you need to total are scattered across a worksheet.

• Example formula: =SUM(A1:A3, C1:C3, E5) adds two ranges and one single cell.
• To edit an existing SUM, double-click the cell or select it and edit in the formula bar; then use the mouse to add or remove ranges.

Use non-contiguous sums sparingly in large models—many separated references make formulas harder to audit. When you need to add many scattered cells regularly, consider structuring data so sums can use contiguous ranges or helper columns.

Conditional totals: SUMIF and SUMIFS

When you want totals that meet a condition—such as "only sales above 100"—use SUMIF or SUMIFS. SUMIF handles a single condition; SUMIFS supports multiple conditions.

• SUMIF syntax: =SUMIF(range, criteria, [sum_range]).
• SUMIFS syntax: =SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...).

If you need help choosing between these functions and other approaches, see the comparison on When to use SUMIF vs SUMIFS.

Other techniques: SUMPRODUCT and summing visible cells

SUMPRODUCT multiplies corresponding elements in arrays then sums the results—useful when you need weighted totals. For examples and patterns, consult How to use SUMPRODUCT to multiply then sum.

If your sheet uses filters or you want to ignore hidden rows, the SUBTOTAL and AGGREGATE functions compute totals only for visible cells. For a practical guide, see How to sum visible cells only (SUBTOTAL and AGGREGATE).

Step-by-step: common SUM workflows

Below are reusable, numbered procedures you can apply immediately.

1. Create a running total for column B:
   1. In cell C2 enter =SUM($B:B2).
   2. Copy the formula down column C; each row shows the cumulative total up to that row.
2. Sum only positive values in a range:
   1. Use =SUMIF(A1:A10, ">0").
3. Sum by condition on another column:
   1. Example: total amounts in B when Category in A equals "Office": =SUMIF(A1:A100, "Office", B1:B100).

Common errors and troubleshooting

When SUM does not return the expected result, one of these issues is often responsible.

• Numbers stored as text: Cells that look numeric but are text are ignored by SUM. Fix: convert them using Paste Special > Values with multiplication by 1, Text to Columns, or VALUE().
• #VALUE! or #NAME? errors: These indicate invalid references, typos in function names, or text where Excel expected numbers. Check the referenced cells and spelling.
• Hidden rows still included: SUM includes hidden cells. Use SUBTOTAL or AGGREGATE to ignore filtered rows; see the link to summing visible cells above.
• Zero or incorrect total: Verify ranges do not include empty columns or wrong addresses; use the Evaluate Formula tool (Formulas tab) to step through complex expressions.
• Rounding differences: If displayed numbers are rounded but underlying values are not, use ROUND within SUM, for example =SUM(ROUND(A1:A10,2)), entered as an array in some versions, or round upstream.

Checklist before you press Enter

• Is the referenced range correct and contiguous when you expect it to be?
• Are all numeric cells truly numbers (not text)?
• Do you need to include or exclude hidden rows?
• For conditional sums, are your criteria ranges aligned and the same size as your sum_range?
• Would a different function (SUMIF/SUMIFS, SUMPRODUCT, SUBTOTAL) be a better fit?

Closing: integrate SUM into reliable spreadsheets

SUM is a fundamental Excel function but applying it well requires attention to data types, ranges, and the context of hidden or conditional data. Use AutoSum for speed, SUMIF/SUMIFS for conditional needs, and SUMPRODUCT or SUBTOTAL when the task calls for multiplication or visible-only totals. For quick reference on related techniques, consult the linked guides on AutoSum and keyboard shortcuts in Excel, When to use SUMIF vs SUMIFS, How to use SUMPRODUCT to multiply then sum, and How to sum visible cells only (SUBTOTAL and AGGREGATE).