How to sum each row with one formula in Excel
Excel formula
=B2:B6+C2:C6+D2:D6Add the columns as ranges and let the result spill, =B2:B6+C2:C6+D2:D6, wrapped in ARRAYFORMULA in Google Sheets.
- Result on the example
- a 5-row list that spills from E2 in
E2 - Works in
- Every current version
- Google Sheets
- Different formula, see below
See it work
The formula is already in E2. Change any input and the result updates, or edit the formula to experiment.
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.
=B2:B6+C2:C6+D2:D6Result{5 rows × 1 col} 149420, 126130, 167750, 100550, …
B2:B65 rows × 1 col: $48,200, $39,650, $55,900, $31,480, …A range: 5 cells in one column.
C2:C65 rows × 1 col: $51,350, $42,100, $53,240, $33,950, …A range: 5 cells in one column.
B2:B6+C2:C6{5 rows × 1 col} 99550, 81750, 109140, 65430, …The + operator adds.
D2:D65 rows × 1 col: $49,870, $44,380, $58,610, $35,120, …A range: 5 cells in one column.
B2:B6+C2:C6+D2:D6{5 rows × 1 col} 149420, 126130, 167750, 100550, …The + operator adds.
In Google Sheets
Google Sheets needs a different formula: =ARRAYFORMULA(B2:B6+C2:C6+D2:D6). On the same data it gives the same result. Here it is running in Google Sheets mode.
Other ways to do it
Each one was run on the same example sheet.
| Formula | Result | When to use it |
|---|---|---|
| =SUM(B2:D2) | $149,420Different result | 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.
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 each row with one formula
E2, write one formula that returns the total of Q1 to Q3 for every rep, spilling down to E6.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.
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.