Sum in Excel in Different Rows: Quick Methods

How-to basics · Updated November 20, 2026

To add values from separate rows in Excel, enter a SUM formula that names the cells you want to include. For example, with amounts in B2, B5, and B8, type =SUM(B2,B5,B8) in the result cell and press Enter; if those cells contain 12, 18, and 25, the result is 55. You can also total a continuous block of rows, add selected cells across columns, or calculate a separate total for each row.

What You’ll Need

  • An Excel workbook open in Microsoft Excel for Microsoft 365 on Windows. Excel for Mac and Excel for the web also support the formulas in this guide, though some keyboard shortcuts and selection steps differ.
  • A clear idea of which rows or cells should be included. It helps to have a labeled column for the values and an empty cell for the answer.
  • Numeric values in the cells you plan to add. SUM ignores blank cells and text entries, so check that amounts have not been stored as text.

Step-by-Step

  1. Identify the cells to total. Look at the worksheet and write down the addresses of the values you want to add. For example, you might want the figures in B2, B5, and B8 while leaving the other rows out. Decide whether you need one grand total or a different result for each row. This distinction matters: one formula can add selected cells, while a row-by-row total needs a formula in each result row.
  2. Choose an empty result cell. Click the cell where the answer should appear, such as B10. Keep the result outside the set of cells being added. If the formula sits inside its own total range, Excel may report a circular reference or give an incorrect setup. If the result belongs in a separate summary area, add a label such as “Selected total” beside it so you can recognize what the number represents later.
  3. Add values from nonadjacent rows with SUM. With the values in B2, B5, and B8, enter =SUM(B2,B5,B8) in the result cell. The comma separates the individual cell references; it does not mean the cells have to be next to one another. Press Enter to calculate. If B2 is 12, B5 is 18, and B8 is 25, Excel returns 55. To include another selected row, add its cell address inside the parentheses, separated by a comma.
  4. Use a range when the rows are next to each other. If you want every value from B2 through B8, with values in B2:B8, enter =SUM(B2:B8). The colon means “from the first cell through the last,” so all cells in that span are included, including rows between the endpoints. For example, if B2:B8 contains 12, 4, 18, 6, 25, 3, and 2, the result is 70. Do not use a continuous range if some intervening rows should be excluded; list only the intended cells instead.
  5. Sum several columns across selected rows. If each chosen row contains multiple values, you can give SUM multiple ranges or individual references. With values in B2:D2 and B5:D5, enter =SUM(B2:D2,B5:D5). This totals all cells in those two rows across columns B to D while skipping rows 3 and 4. If the first range contains 10, 5, and 2 and the second contains 7, 3, and 1, the answer is 28. Check the range endpoints carefully so you do not include a neighboring label or an unrelated column.
  6. Calculate a total separately on every row. To total each row across several columns, put the first result beside that row. With numbers in B2:D2, enter =SUM(B2:D2) in E2. If the three values are 10, 5, and 2, E2 shows 17. Select E2 and drag the small square at its lower-right corner down to fill the formula into later rows. Excel adjusts the row references automatically, so the formula in E3 totals B3:D3. You can also copy E2, select the destination cells below it, and paste.
  7. Use AutoSum for a quick continuous total. Click the empty cell below a vertical run of numbers or to the right of a horizontal run. On Windows, press Alt+=, or select Home > AutoSum. Excel proposes a range; check that the highlighted cells are exactly the ones you intend to add, then press Enter. On Mac, the AutoSum shortcut is Command+Shift+T; you can also use Home > AutoSum. AutoSum is convenient for adjacent values, but for separated rows, edit the proposed formula or type the cell references yourself.
  8. Verify the result and save. Click the total cell and inspect the formula bar to confirm that the references match your intended rows. Compare the displayed result with a simple hand calculation or a small test using a calculator. If you later change a value in one of the referenced cells, Excel recalculates the total automatically. Save the workbook after confirming that the formula includes the correct cells.

Common Mistakes

Using a range that includes unwanted rows: A formula that totals B2 through B8 includes every cell in that span. If rows 3, 4, 6, or 7 should be left out, a continuous range will overcount. Use separate references for nonadjacent rows, or select only the required areas when building the formula.

Leaving out a cell or adding one twice: A long list of addresses is easy to mistype. Check each reference against the worksheet, especially when similar rows contain subtotals or headings. Including a subtotal as well as the detail values used to calculate it can double-count the same amounts.

Typing a formula without the equals sign: Excel treats an entry that does not begin with an equals sign as text or a label rather than a calculation. If the cell displays the formula characters instead of an answer, check that the entry begins with an equals sign and that the cell is not formatted as Text.

Including the result cell in the total: If a formula in B10 adds a range that includes B10, the formula refers to itself. Keep the output cell separate from the inputs, and remove any circular reference if Excel warns about one.

Assuming every visible number is numeric: A value pasted from a website or imported from another file may be stored as text. SUM ignores text in referenced cells, which can make the result smaller than expected. Also check whether filters or hidden rows have changed what you expect to include; SUM adds referenced values regardless of whether their rows are hidden.

Tips

Use the simplest formula that matches the layout. A continuous block is easiest to maintain as a range; a small number of scattered cells is clear as a list of references. If there are many scattered entries, consider adding a helper column that marks the rows to include, or reorganize the data into a single clean list. A tidy layout is often easier to audit than a formula with dozens of addresses.

When entering a formula, you can type the opening part and click the cells or drag across ranges rather than typing every address. For a nonadjacent selection, hold Ctrl on Windows while clicking each cell; on Mac, hold Command while clicking. Then confirm the references in the formula before pressing Enter. This can reduce typing errors, but it does not replace checking that each selected cell belongs in the total.

Use labels near the result to explain the scope, such as “April rows only” or “Selected departments.” If you copy row totals down, check the first few copied formulas. Relative references should change to the corresponding row, while a reference that should stay fixed may need dollar signs. For example, if every row should use a fixed rate in F1, the reference can be written as $F$1; that locks the reference when the formula is copied. Apply this only when the calculation actually needs a fixed cell.

Keep source values and totals visually distinct. Number formatting, a bold total row, or a border can make it easier to spot the result, but formatting does not change which values the formula adds. If the workbook will be updated regularly, leave room for new data and use a clearly defined range so future entries are not accidentally excluded from the calculation.

Troubleshooting

The answer is zero or too small: First click each source cell and check whether its contents are numeric. If a number is stored as text, Excel may align it differently from nearby numbers or show a warning indicator. You can re-enter the value, or select the affected cells and use the warning menu if Excel offers a Convert to Number option. Then verify that the formula refers to those cells and not to a neighboring blank column.

The formula appears as text in the cell: Check that the cell format is not Text. Change it to General from Home > Number Format, then edit the cell and press Enter so Excel evaluates the formula. Also check whether Show Formulas is turned on. On Windows, Ctrl+` toggles formula display; on a Mac, Control+` is commonly used. If the whole sheet shows formulas, toggle that setting off and inspect the result again.

Excel shows an error or asks about a circular reference: Check for a missing closing parenthesis, a misspelled cell address, or a result cell included among the inputs. In a SUM formula, separate arguments with commas in the standard Microsoft 365 Windows setup. If your regional settings use a different list separator, Excel may require semicolons instead; use the separator Excel accepts when you enter a formula.

AutoSum selects the wrong cells: Excel guesses a nearby continuous block, and a blank row or an adjacent column can affect that guess. Before accepting the formula, inspect the highlighted range and adjust it by dragging over the correct cells or by editing the reference. For separated rows, type or select each needed reference instead of relying on the initial AutoSum suggestion.

The total changes when you copy the formula: This is usually because ordinary references adjust as the formula moves. If a particular input must remain fixed, add dollar signs to its row and column reference before copying. Conversely, if each copied total should use that row’s values, do not lock those changing row references. Check the formula in one of the copied cells to make sure the references shifted as intended.

Frequently Asked Questions

How do I add only certain rows in Excel?
Enter a SUM formula with the cell from each row you want to include, separating references with commas. Check the list to make sure it excludes unwanted rows and does not include a subtotal alongside its underlying values.

Can I sum cells in different rows and different columns?
Yes. You can include individual cell references or multiple ranges in one SUM formula. For instance, you can add a few separated cells from different columns, or include a complete range from each selected row. The result cell should remain outside all the referenced inputs.

What is the difference between a comma and a colon in SUM?
A comma separates individual references or separate ranges. A colon defines a continuous range from one cell to another, including the cells between them. Use a colon only when every cell in that span belongs in the total.

Can I sum rows that are hidden by a filter?
SUM includes values in hidden rows if their cells are in the referenced range. If you need a total that changes to include only visible filtered rows, use SUBTOTAL rather than an ordinary SUM formula, and choose the appropriate function number for the behavior you need.

Why does SUM ignore one of my numbers?
The cell may contain text rather than a number, or it may not be included in the formula’s references. Check the cell’s contents and the formula bar, then correct the value or reference. Also look for leading apostrophes or imported formatting that may have stored the entry as text.

Does this work in Excel for Mac or Excel for the web?
Yes. SUM formulas work in both. The main difference in the steps is selecting cells and using shortcuts: Mac uses Command-click for separate selections, and its AutoSum shortcut differs from Windows. Excel for the web also supports formulas; menu placement and available keyboard shortcuts can vary by browser and operating system.

Related

Try it: the Date Difference Calculator: days, months, years and weekdays between two dates, with the DATEDIF formulas.

More in How-to basics

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

Spotted an error in this guide? Let us know.