Practice free

BYROW function

BYROW runs a calculation on each row of an array and returns the results stacked in one column, such as a total for every row. The calculation must return a single value per row.

Example

Read it as a sentence: go through each row of D2:E7, multiply the Qty by the Unit Value, and show the results, starting with 42 for the first row.

Practice this in the app

Syntax

BYROW(array, lambda)

array (required)
The range or array to process row by row.
lambda (required)
A LAMBDA taking one row and returning a single value.

Returns: A single column with one result per row.

One BYROW formula can fill more than one cell. When the result has several values, they spill into the cells below or to the right of the formula.

What Excel actually returns

Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.

FormulaExcel showsCase
=BYROW({1,2;3,4},LAMBDA(r,SUM(r))) 3 Sum each row
=BYROW({1,2;3,4},MAX) 2 Eta-reduced function
=BYROW(Items!D2:E7,LAMBDA(r,PRODUCT(r))) 42 Product of rows
=BYROW(Data!F4:F5,LAMBDA(r,r)) TRUE Error row
=BYROW({1,2;3,4},LAMBDA(r,r*2)) #CALC! Lambda returns array
=BYROW({1;2},LAMBDA(a,b,a)) #VALUE! Two-parameter lambda

The Items sheet these formulas read

The Data sheet these formulas read

A cell that starts with = holds that formula.

Watch out for

  • The lambda must return a single value per row.
  • Often replaced by simpler array math, but handy for custom row logic.

Where it works

Excel
BYROW arrived in Excel 2024. Older versions do not have it. Dynamic-array LAMBDA family; in 365 and Excel for the web, and Excel 2024. Check older perpetual versions.
Google Sheets
Same name, same behaviour for everyday use.

Part of Logical functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .