Excel BYROW Function
- array - The array or array to process.
- lambda - The lambda function to apply to each row.
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.
To sum each row in a range, you can use the BYCOL function like this:
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.
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:
Because there are 11 rows in data, the result is an array with 11 sums like this:
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:
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:
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.