How to Use BYCOL Function in Google Sheets

This guide will explain how to use the BYCOL function in Google Sheets.

The BYCOL function allows you to evaluate data across columns by applying a given LAMBDA function. This is useful when you need to apply a function on multiple values grouped in columns.

For example, we want to find the sum of each column given an array of random integers. We can use BYCOL to return a single-row array where each cell is the sum of that column. 

In this guide, we will provide a step-by-step tutorial on using the BYCOL function in Google Sheets. We will also cover using the BYCOL function with the FILTER function to find columns in a dataset that meet specific criteria.

The Anatomy of the BYCOL Function

The syntax of the BYCOL function is as follows:

=BYCOL(array_or_range,LAMBDA)

Let’s look at each argument to understand how to use the BYCOL function.

• array_or_range refers to the target range the user wants to be grouped by columns.
• The LAMBDA argument must be a LAMBDA function that is applied to each column in the target range to obtain its grouped value. The LAMBDA function must have exactly 1 name argument and a formula that uses that name.
• Every column should be grouped into a single value.

A Real Example of Using the BYCOL Function in Google Sheets

Using BYCOL to Summarize Columns

Let’s explore a simple example where we can use the BYCOL function to summarize multiple columns in a dataset.

In the table above, we have a dataset with three columns of integers. We want to add a summary row to our sheet that indicates the highest values for each column.

We’ll use the BYCOL function to apply a LAMBDA function that finds the maximum value of each column in the range A1:C3. To achieve this, we can use the following formula:

=BYCOL(A1:C3,LAMBDA(column,MAX(column)))

In the formula above, we’ve selected the range A1:C3 as our first argument. The second argument defines a LAMBDA function that returns the maximum value of a given column using the MAX function.

BYCOL evaluates each column by passing the values through the LAMBDA function. The function then returns a row vector with all the results.

Using BYCOL to Find Columns that Meet Specific Criteria

Next, let’s see how we can use the BYCOL function with conditional LAMBDA functions.

In the sample spreadsheet above, we have a table comparing the performance of three salespeople over four quarters. Each integer represents the number of sales the individual was able to close in a given quarter. We want to find out which of these individuals achieved the goal of an average of over 55 sales per quarter.

We can use the BYCOL function on our dataset to find the average number of sales for each row is higher than 55:

=BYCOL(B2:D5,LAMBDA(col,AVERAGE(col)>55))

In the formula above, we defined a LAMBDA formula that takes a column from the range, computes the average of said column and determines if it is higher than 55. The BYCOL function will then return an array of TRUE or FALSE values, which indicate whether an individual’s average sales has met the specified goal.

Using BYCOL with FILTER

Let’s now look into an example where we can filter for a particular column depending on the results of a BYCOL function.

We can use the BYCOL function result from the previous example to determine which employees are eligible for promotion. For this example, we’ll only consider promoting employees who have met our target number of average sales.

Since our LAMBDA function only outputs TRUE for Alice, she is the only salesperson eligible for promotion. To output her name, we can use the following formula:

=FILTER(B1:D1,BYCOL(B2:D5,LAMBDA(col,AVERAGE(col)>55)))

In the formula above, we used the FILTER function on the range B1:D1. This range is the header row that contains the names of each salesperson. We’ll then use our BYCOL function’s output as the criteria  for filtering. Since the result of our BYCOL function is only TRUE for the first column, the FILTER function will only return the name “Alice”.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the BYCOL function.

How to Use the BYCOL Function in Google Sheets

1. Select the cell you want to output the BYCOL result. In this example, we want to output our result starting at B7.
   Ensure that there are  enough empty cells to the right of this cell to hold the output.
   
2. Next, type the BYCOL function name and enter the range you want to use as the first argument.
   
3. Type ‘LAMBDA(,’ to start the required LAMBDA function. You may then type your preferred name argument to use within the LAMBDA function.
   In our example above, we’ve chosen the name argument ‘col’.
   
4. Provide the formula you want to evaluate as the second argument of the LAMBDA function.
   
5. Hit the Enter key to evaluate the formula. BYCOL will return a single row array with the results of each column.
   
6. To filter columns using the BYCOL function, type the FILTER function name and enter the range you want to filter as the first argument.
   
7. Add the BYCOL function as the second argument. Hit the Enter key to evaluate the FILTER formula.
   

You should now be able to use the BYCOL function yourself to apply a LAMBDA function on multiple rows in a range.

To learn more about using LAMBDA functions to generate arrays, you can read our post on how to use the MAKEARRAY function.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks! 

Get emails from us about Google Sheets.

Our goal this year is to create lots of rich, bite-sized tutorials for Excel users like you. If you liked this one, you'd love what we are working on! Readers receive early access to new content.

Subscribe