Purpose
Return value
Syntax
=BYROW(array,lambda)- array - The array or array to process.
- lambda - The lambda function to apply to each row.
How to use
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.
Examples
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 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(row,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. 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.
BYROW function examples
Sum by week
Remove blank rows
Get row totals
Minimum value if
Row is blank
Count birthdays by year
Sum numbers with text
Count rows that contain specific values
Sum by quarter
Sequence of leap years
Maximum value if
Sum by year
Sum by week number
Related functions
LAMBDA Function
The Excel LAMBDA function provides a way create custom functions that can be reused throughout a workbook, without VBA or macros.
LET Function
The Excel LET function lets you define named variables in a formula. There are two primary reasons you might want to do this: (1) to improve performance by eliminating redundant calculations and (2) to make more complex formulas easier to read and write....
MAP Function
The Excel MAP function "maps" a custom LAMBDA function to each value in a supplied array. The LAMBDA is applied to each value, and the result from MAP is an array of results with the same dimensions as the original array.
SCAN Function
The SCAN function applies a custom calculation to each element in a given array or range and returns an array that contains the intermediate values created during the scan. SCAN can be used to generate running totals, running counts, and other calculations...
REDUCE Function
The Excel REDUCE function applies a custom LAMBDA function to each element in a given array and accumulates results to a single value.
MAKEARRAY Function
The Excel MAKEARRAY function returns an array with specified rows and columns, based on a custom LAMBDA calculation. MAKEARRAY can be used to create arrays with variable dimensions based on a calculation....
BYCOL Function
The Excel BYCOL function applies a LAMBDA function to each column in a given array and returns one result per column as a single array. In the example shown, data is the named range C5:H9.
Note: BYCOL is a beta function available...
BYROW Function
The Excel BYROW function applies a LAMBDA function to each row of a given array and returns one result per row in a single array. In the example shown, data is the named range C5:H15.
...
