How to sum each row with one formula in Excel
Formula (Excel and Google Sheets)
=BYROW(B2:F6,LAMBDA(r,SUM(r)))Use BYROW with LAMBDA: =BYROW(B2:F6,LAMBDA(r,SUM(r))) returns one total per row and spills down, so there is nothing to fill.
- Result on the example
- a 5-row list that spills from H2 in
H2 - Works in
- Excel 2024 and Microsoft 365
- Google Sheets
- Same formula
See it work
The formula is already in H2. Change any input and the result updates, or edit the formula to experiment.
How it works
BYROW hands each row of the range to a LAMBDA as r, and whatever the LAMBDA returns becomes that row's result. Swap SUM for MAX, AVERAGE or your own logic. BYCOL does the same for columns.
=BYROW(B2:F6,LAMBDA(r,SUM(r)))Result{5 rows × 1 col} 37.5, 41, 25, 40.5, …
B2:F65 rows × 5 cols: 8, 7.5, 8, 8, …A range: 5 rows × 5 columns.
LAMBDA(r,SUM(r))LAMBDA(R)LAMBDA: Your own function: name its inputs, then the calculation. Call it with (values) or name it with LET.BYROW(B2:F6,LAMBDA(r,SUM(r))){5 rows × 1 col} 37.5, 41, 25, 40.5, …BYROW: Runs a LAMBDA on each row and returns one result per row.- array
B2:F65 rows × 5 cols: 8, 7.5, 8, 8, … - lambda
LAMBDA(r,SUM(r))LAMBDA(R)
- array
In Google Sheets
The same formula works in Google Sheets: =BYROW(B2:F6,LAMBDA(r,SUM(r))). We ran it in Google Sheets mode on the same data and it returns the same results, spilling 5 rows.
Common mistakes
- The LAMBDA parameter holds a whole row, so use an aggregate like SUM inside;
r*2would return a row, not a single value. - Older Excel versions do not have BYROW, so the cell shows #NAME?.
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
H2, return each employee's weekly hours with a single formula that spills down.Questions
Why not just fill =SUM(B2:F2) down?
You can. BYROW is for when you want one formula that resizes itself, or logic that is longer than SUM.
Checked by running on 2026-11-20: every formula and result on this page was calculated by the Spreadsheet Practice formula engine, in both Excel and Google Sheets modes.