Exceljet

Quick, clean, and to the point

Excel BYROW Function

Excel BYROW function
Summary 

The Excel BYROW function applies a LAMBDA function to each column of a given array and returns one result per column in a single array. In the example shown, data is the named range C5:H9. 

Note: BYROW is a beta function available only through the Insiders channel of Excel 365.

Purpose 
Apply function to row
Return value 
One result per row
Syntax 
=BYROW (array, lambda)
Arguments 
  • array - The array or array to process.
  • lambda - The lambda function to apply to each row.
Version 
Usage notes 

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, BYCOL 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.

Examples

To sum each row in a range, you can use the BYCOL function like this:

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

The BYROW function delivers the contents of range one row at a time to the LAMBDA function, which uses the SUM function to calculate a total for each row. BYROW then delivers the results from the LAMBDA calculation in a single array. If range contains 10 rows, BYROW returns an array that contains 10 sums.

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(ror,AVERAGE(row))) // average

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, but COUNTIF requires a range and won't work with an array.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.