Note: BYCOL is a beta function available only through the Insiders channel of Excel 365. The BYCOL 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 column in array. The lambda function must return a single result for each column, or BYCOL will return a #CALC! error.
Examples
To sum each column in a range, you can use the BYCOL function like this: The BYCOL function delivers the contents of range one column at a time to the LAMBDA function, which uses the SUM function to calculate a total for each column. BYCOL then delivers the results from the LAMBDA calculation in a single array. If range contains 5 columns, BYCOL returns an array that contains 5 sums.
Worksheet example
In the worksheet shown above, the BYCOL function is used to run 5 separate calculations on data, which is the named range C5:H5. In cell C11, the formula below is used to calculate a sum for each column: Because there are 6 columns in data, the result is an array with 6 sums like this: The values in this array spill into the range C11:H11. The other formulas in C12:C14 all follow the same pattern:
Count cells over 90
In cell C15, the formula is a bit more complex. The goal is to count cells in each column with a value over 90, and the formula used is: In this case, LAMBDA runs a custom calculation based on the SUM function, which is used to count the number of values in each column that are greater than 90. See Boolean operations in array formulas for more information. Note: the COUNTIF function could be used instead of SUM, but COUNTIF requires a range and won’t work with an array.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
