# How to sum each row with one formula in Excel

Add the columns as ranges and let the result spill, `=B2:B6+C2:C6+D2:D6`, wrapped in ARRAYFORMULA in Google Sheets.

**Excel:** `=B2:B6+C2:C6+D2:D6` (Every current version)

**Google Sheets:** `=ARRAYFORMULA(B2:B6+C2:C6+D2:D6)`

## Example

Sample data (row 1 is the header):

| A | B | C | D | E |
| --- | --- | --- | --- | --- |
| Rep | Q1 | Q2 | Q3 | Total |
| Ana | 48200 | 51350 | 49870 |  |
| Ben | 39650 | 42100 | 44380 |  |
| Cara | 55900 | 53240 | 58610 |  |
| Dev | 31480 | 33950 | 35120 |  |
| Eli | 46720 | 45060 | 47930 |  |

Formula in E2: `=B2:B6+C2:C6+D2:D6`

Result (spills 5 × 1): $149,420; $126,130; $167,750; $100,550; $139,710

## How it works

Adding three columns of five cells gives five sums, one per row, and Excel 365 spills them into E2:E6. One formula covers every row, and if you extend the ranges it covers new reps too. Google Sheets needs ARRAYFORMULA to return more than one result from arithmetic on ranges.

## Other ways

- `=SUM(B2:D2)`: The classic approach: one SUM per row, filled down. Works everywhere. (Returns only the first row; fill it down to E6 for the rest.)

## Common mistakes

- SUM can't do this: `=SUM(B2:D6)` adds the whole block into one number.
- If anything sits in E3:E6, the spill is blocked and E2 shows #SPILL!. Clear the cells below.

## Questions

**What if there are many columns?**

In Excel 365, `=BYROW(B2:D6,LAMBDA(r,SUM(r)))` sums each row of any width. In Google Sheets, BYROW works the same way.

**How do I total each column instead?**

Use `=BYCOL(B2:D6,LAMBDA(c,SUM(c)))` in Excel 365 or Google Sheets, or one SUM per column.

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-each-row-with-one-formula