Spreadsheet Practice

How to sum non-adjacent cells in Excel

Formula (Excel and Google Sheets)

=SUM(B5,B8,B11)

Separate the cells or ranges with commas inside SUM, as in =SUM(B5,B8,B11); a colon would add everything in between.

Result on the example
$84,970 in E1
Works in
Every current version
Google Sheets
Same formula

See it work

The formula is already in E1. Change any input and the result updates, or edit the formula to experiment.

How it works

SUM takes up to 255 separate arguments, and each one can be a single cell or a range. Commas list them one by one, so =SUM(B5,B8,B11) adds the three subtotals and nothing between them. You can mix ranges and cells: =SUM(B2:B4,B6).

=SUM(B5,B8,B11)

Result84970

  1. B5$59,990

    The value in B5.

  2. B8$10,505

    The value in B8.

  3. B11$14,475

    The value in B11.

  4. SUM(B5,B8,B11)84970

    SUM: Adds numbers and ranges. Skips text and blanks.

    • number1B5$59,990
    • number2B8$10,505
    • number2B11$14,475

In Google Sheets

The same formula works in Google Sheets: =SUM(B5,B8,B11). We ran it in Google Sheets mode on the same data and it returns $84,970.

Other ways to do it

Each one was run on the same example sheet.

FormulaResultWhen to use it
=SUMIF(A2:A11,"*subtotal",B2:B11)$84,970Adds every row whose label ends in "subtotal", so a new section is picked up without editing the formula.
=SUM(B2:B11)$169,940Different resultA plain range adds the detail lines and the subtotals. Counts every amount twice: 169,940.

Common mistakes

  • A colon instead of a comma changes the meaning: =SUM(B5:B11) adds every row from 5 to 11, detail lines included.
  • To build the formula by clicking, type =SUM(, click the first cell, then hold Ctrl (Cmd on a Mac) while you click the others.

Try it yourself

Same data, empty answer cell. Your formula is graded instantly, then re-checked on changed data, so a typed-in number won't pass.

Sum non-adjacent cells

In E1, add up only the three subtotal rows (B5, B8 and B11).

Questions

How do I sum two separate ranges?

List both: =SUM(B2:B4,B6:B7).

Can I use + instead?

=B5+B8+B11 gives the same total, but returns #VALUE! if one of the cells holds text. SUM skips text.

Checked by running on 2026-10-30: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.