Excel Sum a Range of Cells: Formulas, Tips, and Fixes

Formulas & functions · Updated November 18, 2026

To add a range of cells in Excel, use the SUM function: if the numbers are in A1 through A10, enter =SUM(A1:A10) in an empty cell and press Enter. For example, if A1:A3 contain 12, 8, and 5, the formula returns 25.

What SUM Does

SUM adds numeric values and returns one total. Its usual form is =SUM(number1,[number2],...): the first argument is required, while later arguments are optional. An argument can be a cell, a range, or another numeric value. In everyday use, a colon identifies a continuous range, so A1:A10 means every cell from A1 to A10, including both endpoints.

For a vertical list, place the formula in a cell outside the list, such as beneath it. For a horizontal list, put the result at the end of the row or elsewhere. A formula can refer to cells above, below, beside, or on another worksheet; the important point is to avoid including the result cell in its own range, which would create a circular reference.

SUM is generally preferable to typing an addition expression such as =A1+A2+A3. The function is easier to extend when more rows are added, and it is less likely to omit a cell in a long list. It also ignores text and blank cells in a referenced range, whereas a direct arithmetic expression involving a text value may return an error.

Enter a SUM Formula

To create the formula manually in Excel for Microsoft 365 on Windows:

  1. Select the cell where you want the total to appear.
  2. Type =SUM(, then select or type the cells to add.
  3. Type the closing parenthesis and press Enter.

For example, with daily amounts in B2:B8, enter =SUM(B2:B8) in B9. If the amounts are 10, 15, 12, 8, 20, 5, and 10, B9 displays 80. You can also type the complete formula directly rather than selecting the cells with the mouse.

Excel uses a colon between the first and last cells of a continuous range. Commas separate distinct arguments. In some regional settings, Excel uses semicolons rather than commas as the argument separator; follow the separator Excel accepts in your installation. The colon used for a range remains the same.

Use AutoSum to Add a Column or Row

AutoSum inserts a SUM formula based on nearby values, which is convenient when you want a quick total without typing the range. Select the empty cell directly below a column of numbers, or immediately to the right of a row, and choose Home > AutoSum in the Editing group. Check the outlined range Excel proposes, then press Enter to accept it. If the outline misses a cell or includes a heading, select the correct range before confirming.

On Windows, Alt+= is the AutoSum keyboard shortcut. On Mac, use Command+Shift+T. Shortcuts can depend on keyboard layout and Excel configuration, so the ribbon command is a reliable alternative. AutoSum is also available from the Formulas tab.

AutoSum makes a guess based on adjacent data; it does not know which values you intended to include. A blank row can make Excel stop its suggested range early. Likewise, a nearby number in another section might be included if the layout is unclear. Inspect the formula and highlighted cells before accepting it, especially in a worksheet with headings, subtotals, or gaps.

Add Separate Ranges or Individual Cells

A SUM formula can add more than one range. With morning amounts in A2:A4 and afternoon amounts in C2:C4, enter =SUM(A2:A4,C2:C4). If the first range contains 4, 6, and 10 and the second contains 3, 7, and 10, the result is 40. The same pattern works for individual cells: with amounts in A1, C1, and E1, =SUM(A1,C1,E1) adds just those three cells.

For data on a different worksheet, include the sheet name followed by an exclamation mark. With amounts in B2:B10 on a sheet named January, enter =SUM(January!B2:B10) to total that range. If a sheet name contains spaces, enclose the name in single quotation marks; for example, a range on a sheet named January Sales is written 'January Sales'!B2:B10. You can also build a cross-sheet reference by selecting the cells while editing the formula.

When you need to add values that meet a condition rather than every value in a range, use a conditional sum function. With regions in A2:A6 and sales in B2:B6, enter =SUMIF(A2:A6,"East",B2:B6) to add sales for rows labeled East. If those rows have sales of 100 and 50, the result is 150. SUMIF handles one condition; SUMIFS is available when you need multiple conditions, such as a region and a date period.

Choose the Right Range and Keep It Updated

A formula only adds the cells it references. If your list is in B2:B20, =SUM(B2:B20) will not include a new amount typed in B21. You can change the endpoint, use a larger range that leaves room for future entries, or convert the list into an Excel table. Avoid using an entire column automatically if it could include unrelated numbers or the formula cell itself.

For a table named Sales with a column titled Amount, use =SUM(Sales[Amount]) to total that column. This structured reference expands as table rows are added, making it useful for lists that grow over time. The table must actually have the name Sales and a column header Amount; Excel usually assigns a different default table name until you rename it.

When copying a formula, consider whether its references should move. A reference such as B2:B20 is relative, so copying the formula one column to the right changes it to C2:C20. To keep the range fixed while copying, use dollar signs: with values in B2:B20, =SUM($B$2:$B$20) always refers to those same cells. Press F4 while editing a reference to cycle through reference styles in Windows; on some laptops, you may need Fn+F4. Mac keyboard behavior varies by keyboard settings.

Sum Only Visible or Filtered Rows

SUM includes values hidden by a filter or manually hidden rows. If you want a total that responds to filtering, use SUBTOTAL instead. With amounts in B2:B10, enter =SUBTOTAL(109,B2:B10); it adds visible values and excludes rows hidden manually as well as rows removed by a filter. The function number 9 also sums filtered-visible rows but includes manually hidden rows. For many filtered lists, this distinction determines whether the displayed total matches what is on screen.

SUBTOTAL is not a replacement for every SUM formula. If you need the grand total regardless of which records are visible, SUM is often the straightforward choice. If the intended total should change as you filter a list, use SUBTOTAL and verify its function number. A worksheet can show different totals for the same range because the functions answer different questions.

Common SUM Problems and How to Fix Them

  • The total is too small: Check the formula’s highlighted range for omitted rows, a blank that interrupted AutoSum’s guess, or values entered below the endpoint. Also check whether numbers are stored as text, which SUM does not treat as numeric values.
  • The result is zero: The referenced cells may be blank, contain text-formatted numbers, or be outside the range you intended. A green error indicator or a warning icon may offer a conversion option. Verify the cell contents and number format; changing a format alone does not always convert text into a number.
  • The formula appears as text: The cell may be formatted as Text, or the entry may begin with an apostrophe. Change the cell format to General, then edit the formula and press Enter again. Merely changing the format after entering a formula may not recalculate it as a formula until you re-enter it.
  • The result shows ####: This usually means the column is too narrow to display the formatted result, rather than that SUM failed. Widen the column or use a shorter number format.
  • The result is an error: SUM can propagate errors from cells in its range, such as #VALUE! or #N/A. Find and correct the underlying error if appropriate. Do not hide it without checking, because the error may signal missing or invalid source data.
  • The total changes unexpectedly: Check whether the formula includes the total cell, whether rows were inserted or deleted, and whether a filter changed which records are visible if the formula uses SUBTOTAL.

To inspect a formula, select its result cell and look in the formula bar. Excel also outlines referenced cells while you edit a formula. These checks are useful before relying on a total in a report: a plausible-looking answer can still come from a range that stops one row too soon.

SUM Compared with Other Ways to Total

For a complete set of numeric cells, SUM is the clearest general-purpose option. Typing plus signs works for a few isolated cells, but becomes difficult to audit and maintain as the list grows. AutoSum is a shortcut for inserting a SUM formula, not a different calculation method. SUBTOTAL is suited to totals that should respond to filtering, while SUMIF and SUMIFS are for totals based on criteria.

These functions are not interchangeable. For instance, using SUM on a filtered list still includes filtered-out values; using SUBTOTAL with the appropriate function number changes that behavior. A conditional sum, meanwhile, selects records based on their contents rather than their visibility. Choose based on what the total is supposed to represent, then make the formula’s range and criteria easy for another person to verify.

Frequently Asked Questions

What is the formula to sum cells in Excel?

Use SUM with the first and last cells separated by a colon. If the values are in C1:C12, enter =SUM(C1:C12) in another cell. The formula adds numeric values in that range.

How do I sum an entire column?

If the data is in column B, =SUM(B:B) adds numeric values in that column. Put the formula outside column B, or it can include itself and cause a circular reference. A defined range or table column is often safer when the column also contains unrelated numbers.

Does SUM ignore blank cells and text?

Yes. SUM ignores blank cells and text values within a referenced range, while adding numeric values. A number stored as text is not necessarily counted as a number, so convert or correct the source value if it should be included.

How can I sum cells that are not next to each other?

List each cell or range as a separate SUM argument. For example, with values in A1:A3 and C1:C3, use =SUM(A1:A3,C1:C3). If Excel uses semicolons as its argument separator in your regional settings, use that separator instead of commas.

Why does AutoSum give the wrong total?

AutoSum chooses a suggested range based on nearby cells. Blank rows, headings, or adjacent numbers can lead it to miss or include cells. Before pressing Enter, inspect the outlined range and adjust it if necessary.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.