How to Use BYCOL Function in Google Sheets

The BYCOL function in Google Sheets is a function that is used to operate an Array or group of columns. BYCOL function makes your operation very fast and efficient as it simultaneously gives results on groups of columns. Using the BYCOL function is also easy, you need to know which operation you are going to use, then follow the syntax and get the instant result. In this guide, we will tell you in different ways how to use the BYCOL function in Google, so let’s get started.

Benefits of BYCOL Function in Google Sheets

One of the major advantages of the BYCOL function is that you can get the results in a group of columns simultaneously by just applying the function in a single cell. It is not only time efficient but also automates the data to make it errorless. You don’t need to insert a function individually in each cell or to drag the formula over the other cells, just you can use the BYCOL function to get the result in the group of cells. So, let’s go through the following article on how to use the BYCOL function in Google Sheets.

Syntax

=BYCOL(Array_or_range, lambda)

Array_or_range:

It is the data range on which the user wants to be grouped by column.

Lambda

Lambda is a helping custom function, that has two arguments. It must have a lambda-named function applied on the given range to be grouped by column. Secondly, it uses 1 name argument that is used by the function to solve the query.

Step-by-Step Procedure – How to Use Bycol Function in Google Sheets

You can use the BYCOL function in so many ways in Google Sheets, like by applying different conditions that depend on the criteria that you are giving. In this article on how to use the BYROW function in Google Sheets, we will see a couple of examples to enhance your understanding of the BYCOL Function.

• Simple usage of the BYCOL function in Google Sheets
• Using BYCOL function conditionally in Google Sheets

Simple usage of BYCOL Function in Google Sheets

In this example of using the BYCOL function, we will simply use the BYCOL function to find how to be grouped by columns in Google Sheets.

Step 1

In the following example data, we have data and several columns as you can see below on which we want to run a primary function simultaneously so let’s see how we can do this by using the BYCOL function in Google Sheets.

Step 2

Place your cursor in the resultant cell of the first column and start the “BYCOL” function by just writing BYCOL with an equal sign.

Step 3

After starting the function, select the range array according to the syntax for which you want to get the result as I have selected below.

Step 4

The second argument of the BYCOL function is the LAMBDA function, so here now we will start the LAMBDA function after just putting a comma.

Step 5

According to the arguments of the LAMBDA function, we need to pick an alphabet as a variable to run the query for the named function as here we are using “X”.

Step 6

After writing the variable, now it’s time to run the primary function that you want to apply to the array. As we need to make a sum of this array range, here we have run the sum function where we will put the same variable as the argument we used for the LAMBDA function. Now close all the brackets that we have opened into the syntax.

Final Formula: =BYCOL(B4:E8,LAMBDA(X,sum(X)))

Step 7

Our syntax has been completed now we just have to press the Enter key which will give us quick results for the entire array range into the columns as can be seen below.

Using BYCOL function conditionally in Google Sheets

There are so many built-in functions in Google that are used conditionally, similarly if you want to get the result conditionally while using the BYCOL function in Google Sheets, you may do so with the following steps.

Step 1

In this example data we see, here we have a list of different products having several sales for the year categorized in quad quarters. Here we will find the products that have sales greater than 5, let’s see how we can find them by using the BYCOL function.

Step 2

First of all, we will start the BYCOL function in a cell by just writing the name of the function with an equal sign.

Step 3

As we know the first argument of the BYCOL function is selecting the array range for which we want to get the results.

Step 4

According to the syntax, we will start the LAMBDA function into the syntax after giving the array range then we will use the function that we want to apply to the range. As we have to count the number of cells conditionally, here we will use “COUNTIF” in the following example.

Step 5

As we need to count the number of products that have sales greater than 5 so, here we have written a condition of “<5” after starting the COUNTIF function.

Final Formula: =BYCOL(B4:E8,LAMBDA(Y,COUNTIF(Y,”<5”)))

Step 6

We are almost done now, as we click on the Enter button, we will get all the results for the entire range. As we can see in the following picture, in the first quarter only 4 products have several cells greater than 5 and so on. In this way, we can also use the BYCOL function in a good sheet.

How to Use Bycol Function in Google Sheets – FAQs

Q: What is another way to start the BYCOL function instead of writing a name in Google Sheets?

A: Although, the most common and easiest way of starting any function in Google Sheets is just by writing the name of the function with an equal sign, if you don’t know the name of the function or forget the function then you can also find the function from the menu bar of Google Sheets and can insert the function without writing its name. Let me show you practically in the following steps, how can you insert a function in Google Sheets from the menu bar of Google Sheets.

Step 1

Place your cursor in the cell where you want to apply the BYCOL function then go into the “Insert” tab of the menu bar of Google Sheets as highlighted below.

Step 2

A small drop-down menu will open, when we click on the Insert tab, and click on the “Functions” option from the drop-down menu of the Insert tab.

Step 3

Another dropdown menu will open where you will see different categories of functions. For your information, the BYCOL function lies in the “Arrays” functions, so here we will click on the “Array” option from the drop-down menu.

Step 4

Now here all the functions regarding the array will appear in the drop-down menu, in these functions, you will find the BYCOL function as highlighted below. Click once on it to insert it.

Step 5

Here you can see the results in the following picture that the BYCOL function has been started now. In this way as well, you can start the BYCOL function in the cell without writing anything.

Conclusion

I hope, now you know how to use the BYCOL function in Google Sheets as we have covered two different examples of using the BYCOL function. If there is any query about the above topic must let us know in the comment section below. Thanks, and keep learning with Office Demy.