BYROW Function

The Excel BYROW function applies a LAMBDA function to each row in array and returns one result per row as a single array. The purpose of BYROW is to process data in an array or range in a "by row" fashion. For example, if BYROW is given an array with 10 rows, BYROW will return an array with 10 results. The calculation performed on each column is provided by a custom LAMBDA function.

The BYROW function takes two arguments: array and lambda. Array is the array or range to process. Lambda is the LAMBDA function that should be run on each row in array. The LAMBDA function must return a single result for each column, or BYROW will return a #CALC! error.

The concept

The BYROW function applies a custom calculation to each row in a range. The calculation itself is determined by the function passed into BYROW. For example, to sum each row in a range, you can use the BYROW function like this:

=BYROW(range,LAMBDA(row,SUM(row)))

The BYROW function delivers the range one row at a time to the LAMBDA function, which uses the SUM function to calculate a sum for each row. The BYROW function stores this result and moves on to the next row. After BYROW has processed every row in the range, all sums are returned in a single array. If the range contains 10 rows, BYROW returns an array that contains 10 sums.

Note: in the formula above, we use the variable name "row" in the LAMBDA function for clarity. However, you are free to use whatever name you like.

Worksheet example

In the worksheet shown above, the BYROW function is used to run 2 separate calculations on data, which is the named range C5:H15. In cell J5, the formula below is used to calculate a sum for each row:

=BYROW(data,LAMBDA(row,SUM(row)))

Because there are 11 rows in data, the result is an array with 11 sums like this:

{432;440;403;455;479;433;426;463;407;431;519}

The values in this array spill into the range J5:J15. The formulas below are other examples of how BYROW can be used on the same data with formulas that follow the same pattern:

=BYROW(data,LAMBDA(row,MAX(row))) // max
=BYROW(data,LAMBDA(row,MIN(row))) // min
=BYROW(data,LAMBDA(row,AVERAGE(row))) // average

Abbreviated "eta" syntax

While BYROW is designed to accept a custom lambda, it will also accept an abbreviated "eta lambda" syntax for simple operations. Using the eta lambda syntax, you can pass a function by name only into BYROW like this:

=BYROW(array,SUM)

This formula is equivalent to the long-form version:

=BYROW(array,LAMBDA(x,SUM(x)))

The Excel formula engine knows how to pass each row into the SUM function, and the result is exactly the same for both formulas. You can use the same syntax to call other functions like this:

=BYROW(array,SUM) // sum each row
=BYROW(array,MAX) // max of each row
=BYROW(array,MIN) // min of each row
=BYROW(array,COUNT) // count of each row

The eta lambda syntax works best with functions that will accept a single argument, such as SUM, AVERAGE, MIN, MAX, COUNT, COUNTA, etc. However, it will not work for all scenarios because the value passed into the function can't be modified in any way. For example, the formula in the next section below can't use the eta lambda syntax.

Count cells over 90

In cell K5, the formula is a bit more complex. The goal in this case is to count the values in each row greater than 90:

=BYROW(data,LAMBDA(row,SUM(--(row>90)))) // count >90

Working from the inside out, a logical expression is used to check all values in row against 90, and the resulting TRUE and FALSE values are coerced to 1s and 0s by the double-negative (--). The SUM function sums the result and returns a count. As before, the LAMBDA is run on each row in the data, so there are 11 results total that spill into the range K5:K15.

See Boolean operations in array formulas for more information about the logic inside of SUM. 

Note: the COUNTIF function could be used instead of SUM. Note that COUNTIF requires a range and can't use an in-memory array so there are some situations where COUNTIF isn't an option.