BYCOL Function

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

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.

The concept

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

=BYCOL(range,LAMBDA(column,SUM(column)))

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

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

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:

=BYCOL(data,LAMBDA(column,SUM(column)))

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

{391,429,347,394,325,363}

The values in this array spill into the range C11:H11. The other formulas in C12:C14 all follow the same pattern:

=BYCOL(data,LAMBDA(column,MAX(column))) // max
=BYCOL(data,LAMBDA(column,MIN(column))) // min
=BYCOL(data,LAMBDA(column,AVERAGE(column))) // average

Abbreviated "eta" syntax

While BYCOL 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 BYCOL like this:

=BYCOL(array,SUM)

This formula is equivalent to the long-form version:

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

The Excel formula engine knows how to pass each column 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:

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

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 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:

=BYCOL(data,LAMBDA(column,SUM(--(column>90)))) // count >90

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.