The Excel BYCOL function applies a user-defined lambda function to each column in an array and returns a horizontal array result.

To define the lambda function, you can use native Excel functions such as SUM, AVERAGE, COUNT, MAX, MIN, LARGE, SMALL, MINIFS, MAXIFS, COUNTIF, COUNTIFS, etc. In short, the function processes a column and returns a single value output.

Why is BYCOL called a Lambda helper function?

BYCOL and LAMBDA are two functions in Excel. While LAMBDA can function as a standalone reusable function, BYCOL applies the lambda function to each column in an array.

Syntax

Here is the syntax and argument explanation of the BYCOL function in Excel (note: not all Excel versions don’t include this function):

=BYCOL(array, lambda)

array: An array or range from which each column will be separated and operated on.

lambda: A user-defined lambda function that takes a column as a single parameter and calculates one result. The syntax is =LAMBDA(parameter1, calculation).

Apply a Custom Calculation to Each Column in Excel Using BYCOL

Let’s say we have the following array for our example:

This array represents the user engagement matrix of a blog, arranged from A1 to E5 in an Excel spreadsheet.

Steps to Sum Each Column:

To calculate the total views, you can utilize the following SUM formula: =SUM(A2:A5)

Now, let’s incorporate it into a lambda function.

Syntax: LAMBDA(parameter1, calculation).

calculation: The calculation mirrors the previously mentioned SUM formula, but we’ll replace A2:A5 with a meaningful name, such as “user_data”. Thus, the calculation will be SUM(user_data). It’s important to avoid spaces, cell references, or texts starting with numbers while naming references to prevent errors.

parameter1: In the lambda function, use the same name, i.e. “user_data”, in parameter1, resulting in the following lambda function to sum a column: LAMBDA(user_data, SUM(user_data))

Now, use this lambda function as a value in BYCOL, as shown below:

=BYCOL(A2:E5, LAMBDA(user_data, SUM(user_data)))

Where:

• array: A2:E5
• lambda: LAMBDA(user_data, SUM(user_data))

Enter this formula in cell A6 to obtain the total of each column under their respective columns.

Finding AVERAGE, MIN, MAX, COUNT, COUNTA, SMALL, and LARGE Using Excel BYCOL Function

We already have a BYCOL formula to sum each column in an array. To obtain the average of each column, replace SUM with AVERAGE.

Example:

=BYCOL(A2:E5, LAMBDA(user_data, AVERAGE(user_data)))

Similarly, you can utilize the MIN, MAX, COUNT, and COUNTA functions in the Excel BYCOL lambda helper function.

To use the SMALL and LARGE functions with BYCOL to obtain the smallest or the largest nth value in each column:

=BYCOL(A2:E5, LAMBDA(user_data, LARGE(user_data, 2)))

The above formula returns the second-largest value in each column. To obtain the second smallest value in each column, replace LARGE with SMALL.

Excel BYCOL and Lambda Function Using Conditions

An important aspect when using BYCOL in Excel is that the lambda function must return a single value. You can utilize any function that takes a column as a parameter.

For instance, consider the following sample data containing the marks of four students in three subjects:

The data range is A1:E4. Let’s determine in how many subjects each student scored above 80.

For this purpose, you can use the following BYCOL Lambda helper function in cell B5:

=BYCOL(B2:E4, LAMBDA(score, COUNTIF(score, ">=80")))

Similarly, you can use MINIFS, MAXIFS, SUBTOTAL, and other functions with Excel BYCOL.

Resources

• Excel BYROW Lambda Helper Function
• Running Total Array Formula in Excel [Formula Options]
• Running Count of Occurrences in Excel (Includes Dynamic Array)
• Creating a Running Balance with Dynamic Array Formulas in Excel