BYROW function
Quick answer
BYROW runs a LAMBDA on each row of a range and returns one result per row, all from one spilling formula.
- Syntax
=BYROW(array, lambda)- Most common use
=BYROW(B2:D4,LAMBDA(r,SUM(r)))returns 36 (Each rep's total)
Use it for row totals, row maximums or any per-row test where you would otherwise fill a formula down, and with functions like SUM that on their own would collapse the whole range into one number.
- Available in
- Excel 2024 and Microsoft 365
- Category
- Dynamic array functions
- Returns
- A one-column array with one result per row.
- Google Sheets
- Available in Google Sheets.
Syntax
=BYROW(array, lambda)
| Argument | Required? | What it is |
|---|---|---|
array | Required | The range or array to process row by row. |
lambda | Required | A LAMBDA with one input, the row, that returns a single value. |
Examples
Each result below is calculated from the sample sheet by our formula engine, the same one that grades the practice.
| Cell | Formula | Result | What it shows |
|---|---|---|---|
| E2 | =BYROW(B2:D4,LAMBDA(r,SUM(r))) | 36spills 3 × 1 | Each rep's total |
| F2 | =BYROW(B2:D4,LAMBDA(r,MAX(r))) | 15spills 3 × 1 | Each rep's best month |
| H2 | =SUM(BYROW(B2:D4,LAMBDA(r,MIN(r)))) | 24 | The worst months, added up |
Try it live
This is the sample sheet. Change any number or text, or edit a formula, and the results update.
Show the sample data as a table
| Rep | Jan | Feb | Mar | Result | |||
| Ana | 12 | 15 | 9 | 36 | 15 | 24 | |
| Raj | 8 | 11 | 14 | 33 | 14 | ||
| Lee | 20 | 7 | 13 | 40 | 20 |
Practice BYROW
Graded instantly, then re-checked on changed data.
Best month per rep
F2, use BYROW to return each rep's highest monthly sales from B2:E6, one result per row.Common mistakes
| What you see | Why, and how to fix it |
|---|---|
| #CALC! | The LAMBDA returns more than one value per row, such as the row itself. Make it return one number or text. |
| #VALUE! | The second argument isn't a LAMBDA with exactly one input. |
Tips
- =BYROW(B2:M20,LAMBDA(r,COUNTIF(r,">0"))) counts the months with sales for each row in one formula.
Questions
How do I sum each row with one formula in Excel?
=BYROW(B2:D10,LAMBDA(r,SUM(r))) spills a total for every row.
Why not just fill down =SUM(B2:D2)?
That works too. BYROW keeps the whole column as one formula, so it grows with a spilled range and can't be overwritten row by row.
Updated Checked by running: every formula on this page was run by our formula engine on .