Spreadsheet Practice

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, …

  1. B2:F65 rows × 5 cols: 8, 7.5, 8, 8, …

    A range: 5 rows × 5 columns.

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

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

    • arrayB2:F65 rows × 5 cols: 8, 7.5, 8, 8, …
    • lambdaLAMBDA(r,SUM(r))LAMBDA(R)

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*2 would 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

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