The Google Sheets function BYCOL is a powerful tool designed to apply a custom function, defined using a LAMBDA expression, across each column of a specified array. This allows for efficient data manipulation and analysis, enabling users to perform calculations relevant to each column individually.

Syntax

BYCOL(array, lambda)  

• array: The input range or array that consists of multiple columns.
• lambda: A LAMBDA function that defines the operation to be applied to each column of the array.

Example #1

=BYCOL(A1:C3, LAMBDA(col, SUM(col)))  

This formula calculates the sum of each column in the range A1:C3, returning a single-column array of sums.

Example #2

=BYCOL(A1:B5, LAMBDA(col, AVERAGE(col)))  

This formula computes the average of each column in the specified range, returning an array of averages.

Example #3

=BYCOL(D1:F10, LAMBDA(col, COUNTIF(col, ">10")))  

This formula counts how many cells in each column of the range D1:F10 contain values greater than 10, returning an array of counts.

Error handling

If an error occurs while processing a column, BYCOL will return an VALUE! error for that particular column. It’s essential to ensure that the LAMBDA function handles possible variations in data type, as operations may not be valid for all data types present in the column.

Conclusion

The BYCOL function is an invaluable addition to the Google Sheets toolbox, allowing users to apply personalized calculations to entire columns efficiently. With the ability to utilize LAMBDA expressions, it opens up new possibilities for data analysis and manipulation, making it easier to derive insights from data sets.