# How to sum non-adjacent cells in Excel

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

**Excel:** `=SUM(B5,B8,B11)` (Every current version)

**Google Sheets:** the same formula works.

## Example

Sample data (row 1 is the header):

| A | B | C | D |
| --- | --- | --- | --- |
| Line | Amount |  | Grand total |
| Salaries | 48200 |  |  |
| Benefits | 9640 |  |  |
| Training | 2150 |  |  |
| People subtotal | =SUM(B2:B4) |  |  |
| Software | 6380 |  |  |
| Hardware | 4125 |  |  |
| IT subtotal | =SUM(B6:B7) |  |  |
| Rent | 12600 |  |  |
| Utilities | 1875 |  |  |
| Facilities subtotal | =SUM(B9:B10) |  |  |

Formula in E1: `=SUM(B5,B8,B11)`

Result: $84,970

## 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)`.

## Other ways

- `=SUMIF(A2:A11,"*subtotal",B2:B11)`: Adds every row whose label ends in "subtotal", so a new section is picked up without editing the formula.
- `=SUM(B2:B11)`: A 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.

## 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.

Every formula and result on this page was checked by running it in the Spreadsheet Practice formula engine on 2026-10-30.

Source: https://spreadsheetpractice.com/formulas/sum-non-adjacent-cells