A wrong total can quietly derail a budget, sales report, or monthly forecast. The good news is that Excel's SUM formula handles most everyday addition tasks cleanly, as long as you reference the right cells.

This guide shows how to add a range, combine separate cells, total multiple columns, use AutoSum, and calculate conditional totals without accidentally including headers or subtotals.

Start with the basic Excel SUM formula

The Excel SUM formula adds numbers from one or more cells or ranges. Its syntax is:

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

For a simple list of values in cells A2 through A10, use:

=SUM(A2:A10)

Every Excel formula must start with an equals sign. In a referenced range, SUM ignores blank cells and text values, so labels such as "Pending" will not be added to the total.

Add a continuous range of cells

A continuous range is the most common use of SUM. If your sales figures are in column B from row 2 to row 12, enter this formula in an empty result cell:

=SUM(B2:B12)

The colon means "through." In other words, B2:B12 tells Excel to add every cell starting at B2 and ending at B12.

To build the formula manually, select the cell where the total should appear, type =SUM(, then click and drag from the first value to the last value. Excel inserts the range reference for you. Type the closing parenthesis and press Enter.

The total updates automatically whenever a value within B2:B12 changes. That is the main advantage over adding numbers with a calculator and typing the result into a cell.

Sum an entire column without including the header

You can add every numeric value in column B with:

=SUM(B:B)

Excel ignores a text header such as "Revenue," so this often works. Still, it is not always the safest choice. A whole-column formula can accidentally include a subtotal, a note formatted as a number, or another calculation placed farther down the sheet.

For a typical worksheet, a bounded range is easier to audit:

=SUM(B2:B1000)

If your data grows regularly, an Excel Table is usually a better long-term option because its references expand with new rows.

Add separate cells and non-adjacent ranges

Sometimes the cells you need are not next to each other. To add selected individual cells, separate each reference with a comma:

=SUM(B2,B5,B9)

This formula adds only B2, B5, and B9. Commas tell Excel that each reference is a separate argument.

You can also combine complete ranges. For example, to add figures from columns B and D while skipping column C, use:

=SUM(B2:B10,D2:D10)

The plus-sign approach also works for a short calculation:

=B2+B5+B9

But SUM is usually the better habit. It is easier to extend, easier to inspect, and less error-prone when your formula grows beyond a few references.

Sum across multiple columns or rows

To add every number inside a rectangular block, reference the top-left and bottom-right cells. For example:

=SUM(B2:E10)

Excel adds all numeric cells from B2 through E10, including every cell in the rows and columns between those two points.

For a row total, use a horizontal range. If January through April values are in B2 through E2, place this formula in F2:

=SUM(B2:E2)

Putting row totals to the right of the data is a practical layout when each row represents a product, customer, employee, or project.

For a column total, use a vertical range:

=SUM(B2:B10)

Place column totals below the data when each column represents a month, department, or category. The best placement is the one that lets a reader scan the report without hunting for the total.

Use AutoSum for the fastest total

AutoSum is the fastest way to create a basic total when your data is laid out cleanly. Select the empty cell directly below a column of numbers, or directly to the right of a row of numbers. Then choose AutoSum from the Home or Formulas tab.

Excel suggests a range, highlights the cells it plans to add, and inserts a SUM formula. Check the highlighted reference, then press Enter to accept it.

On Windows, the Excel SUM formula shortcut is Alt + =. Select the destination cell first, press the shortcut, confirm the suggested range, and press Enter.

A common mistake is accepting AutoSum's first suggestion without checking it. AutoSum may stop at a blank cell, or it may include an existing subtotal directly above the new total. If the highlighted range is wrong, drag to select the correct cells before confirming.

Copy a SUM formula down a list of totals

When each row needs the same type of total, create the first formula once and copy it down. For example, if columns B through E contain monthly values and column F is the row total, enter this in F2:

=SUM(B2:E2)

Then drag the small square at the lower-right corner of F2, called the fill handle, down through the remaining rows. You can also copy F2 and paste it into the cells below.

Excel adjusts relative references automatically. When copied down one row, =SUM(B2:E2) becomes:

=SUM(B3:E3)

You can double-click the fill handle when a neighboring column contains a complete, uninterrupted list of data. Do not rely on this method when the adjacent column has blanks, because Excel may stop copying earlier than you expect.

Keep a fixed range with absolute references when needed

Most ordinary SUM formulas should use relative references. Add dollar signs only when a copied formula must continue pointing to the same cell or range.

For instance, if each row's amount in B2 should be divided by a fixed grand total in B20, use:

=B2/$B$20

When you copy that formula down, B2 changes to B3, B4, and so on, while $B$20 remains fixed. The dollar signs are not required for a normal row total such as =SUM(B2:E2).

Calculate totals that meet a condition with SUMIF and SUMIFS

Use SUM when every numeric cell in a range belongs in the total. Use SUMIF when values must meet one condition, and SUMIFS when they must meet two or more conditions.

Suppose column A contains regions and column B contains sales. To add sales for the East region only, use:

=SUMIF(A2:A20,"East",B2:B20)

Excel checks A2:A20 for "East" and adds the matching values from B2:B20.

For multiple conditions, SUMIFS is the right formula. If column C contains revenue, column A contains regions, and column B contains order values, this formula adds revenue for East orders worth at least 100:

=SUMIFS(C2:C20,A2:A20,"East",B2:B20,">=100")

Keep the ranges the same size. If the sum range runs from row 2 to row 20, each criteria range should also run from row 2 to row 20. Put text criteria in quotation marks, and quote comparison criteria such as ">=100" as well.

Avoid double-counting subtotals in a mixed report

A common reporting error happens when detailed rows and subtotals share the same column. If you use one large SUM range that includes both, Excel adds the underlying values and the subtotal rows, inflating the grand total.

Instead, sum only the detail rows, or keep source data separate from report calculations. A cleaner structure is to place raw transactions in one table, calculate subtotals in a summary area, and build grand totals from clearly identified summary cells.

Fix common SUM formula problems

If a SUM result is zero, too low, or too high, inspect the data before rewriting the formula. SUM itself is simple; the issue is usually the selected range or the way values are stored.

• The total is zero: The cells may contain numbers stored as text. Numbers aligned left by default, apostrophes before values, or imported data with hidden spaces are common clues. Convert those entries to real numbers before summing.
• The total is too low: Check whether the range misses rows or columns. Also remember that AutoSum can stop its selection at a blank cell.
• The total is too high: Look for subtotal or grand-total rows included inside the range. Exclude them rather than trying to compensate with a second subtraction formula.
• You expected only visible filtered rows: Regular SUM includes filtered-out rows. If you need a total of visible rows only, use SUBTOTAL instead of SUM.
• You see #VALUE!: One of the referenced cells or formula arguments may already contain an error. Trace back through the cells feeding the total and fix the underlying error first.

Text that looks like a number is especially deceptive. SUM ignores text in a referenced range, so a cell displaying 1,250 may contribute nothing if it was imported as text rather than stored as a numeric value.

Make SUM formulas easier to maintain

For a growing dataset, convert the source range into an Excel Table. Tables expand as you add rows, which makes totals more durable. A structured reference might look like this:

=SUM(Table1[Amount])

Keep raw data, row-level calculations, and report totals in separate areas. Mixing all three in one long column is how subtotal rows get included by accident and how circular references become harder to spot.

If you need help building or troubleshooting a formula, the Excel Formula Generator: Create, Explain, and Fix Formulas can turn a plain-English request into an Excel formula and explain an existing one. For more advanced calculations that return multiple results, see this guide to the Array Formula in Google Sheets: Examples and How to Use It.

FormulaBerry can also help translate a plain-English request into an Excel or Google Sheets formula when SUMIF and SUMIFS criteria become difficult to manage.

Use SUM confidently for everyday Excel totals

For ordinary totals, select the cells you need and use SUM. Use AutoSum when the adjacent data is clean, combine references with commas when ranges are separate, and use a rectangular reference when you need to add multiple rows and columns at once.

Move to SUMIF for one condition and SUMIFS for multiple conditions. Before trusting any result, confirm that the formula includes the intended detail cells and excludes headers, subtotals, and unrelated calculations.

If you need help constructing, explaining, or correcting a spreadsheet formula, try FormulaBerry to turn the calculation you want into a usable Excel or Google Sheets formula.