The Excel BYCOL function lets you perform column-wise calculations using a custom LAMBDA function. It returns one result per column.
For example, suppose that you are a Sales Manager. You wish to find the monthly sales generated by all your team members in the given quarter. Refer to the following image,
Our formula would be as follows:
=BYCOL(B2:D11, LAMBDA(column, SUM(column)))Where,
- B2:D11 is the range or array
- LAMBDA(totalSale, SUM(totalSale)) is the LAMBDA calculation to be applied to each column of the given array
- SUM(column) is the formula to add all values in the given column
The results are displayed in the following GIF.
Syntax
The BYCOL is a simple logical function with the following syntax. It is used to apply a custom LAMBDA function to each column in the given array.
=BYCOL(array, [function])Where,
- array argument needs to be replaced with the range or array to which we wish to apply the LAMBDA calculation column-wise
- function argument needs to be replaced with the LAMBDA calculation
Important Notes:
- The function displays results across a horizontal array
- The first argument must be a valid array or range
- You may get a #VALUE error if the LAMBDA function is not properly defined
- It ignores all the empty cells in the given column during calculation
How to use the BYCOL Function in Excel
Download the example spreadsheet used to demonstrate the BYCOL function in the upcoming section. Practice is the key to success!
Example 1: BYCOL Function Basics
Suppose that you are preparing marksheets for the students whose scores are given in the following table.
We have the student’s name at the top, across the first row. Now, let us use the BYCOL function to get total marks within a few seconds.
Here are the steps,
- Select the cell B8
- Type =BYCOL
- Select the first option from the popup
- Specify the array B2:K6
- Type , on your keyboard to move to the next argument
- Type LAMBDA
(As the LAMBDA is a built-in function in Excel, you will see the following popup) - Double-click the LAMBDA command from the list
- Specify scores as the first parameter for the LAMBDA function
- Type ,
- Type SUM
(As the SUM is a built-in function in Excel, you will see the following popup) - Choose the first option from the list
- Specify scores
- Complete the bracket for the SUM function using )
- Complete the bracket for the LAMBDA function using )
- Lastly, complete the bracket for the BYCOL function using )
- Hit the Enter key
Our final formula is as follows,
=BYCOL(B2:K6, LAMBDA(scores, SUM(scores)))Where,
- B2:K6 is an array including scores obtained by each student
- LAMBDA(scores, SUM(scores)) is the LAMBDA calculation to be performed on each column
- scores is the first parameter for the LAMBDA function
- SUM(scores) is the second parameter and a formula to calculate the total marks obtained by each student
The results are displayed in the following image,
There are two methods of using the BYCOL function, depending on how you define the function (calculation) to be applied to each column of the given array.
METHOD #1: Custom LAMBDA Calculation
This method requires you to define a custom LAMBDA function within the BYCOL formula. Above, we did the same.
Our formula was as follows:
METHOD #2: Eta LAMBDA Calculation
This method is also known as the “eta-reduced LAMBDA” method. It’s nothing but a simplified way of writing LAMBDA formulas.
For example, instead of writing a long LAMBDA formula within the BYCOL function, I can simply write:
Yes, it is that easy. All you need to do is write SUM, and the BYCOL function will add all the values available across each column of the given array. Refer to the following GIF.
Example 2: Counting the numbers that are greater than the given number
We will consider the same data from the previous example. Here we have the scores obtained by each student in various subjects.
For each student, our task is to count the subjects in which they failed to score at least 40 marks.
Here are the steps,
- Click on the cell B8
- Type =BYCOL
- Select the first option from the popup
- Specify the array B2:K6
- Type , on your keyboard
- Type LAMBDA
(As the LAMBDA is a built-in function in Excel, you will see the following popup) - Double-click the LAMBDA command from the list
- Put score as the first parameter
- Type ,
- Type SUM
(As the SUM is a built-in function in Excel, you will see the following popup) - Type —
(It is a double unary operator to convert Boolean values into numbers) - Specify the condition (score < 40)
(Make sure to use the round brackets) - Close the bracket for the SUM function using )
- Close the bracket for the LAMBDA function using )
- Close the bracket for the BYCOL function using )
- Press the Enter key
Our final formula is as follows,
=BYCOL(B2:K6, LAMBDA(score, SUM(--(score < 40))))Where,
- B2:K6 is the array
- LAMBDA(score, SUM(–(score < 40))) is the LAMBDA formula to count subjects in which the given student couldn’t score at least 40 marks
- score is the first parameter for the LAMBDA function
- SUM(–(score < 40)) counts the number subjects
- score < 40 is the condition to find if the student could score 40+ marks in the corresponding subject
- — is the double unary operator to convert Boolean values (TRUE or FALSE) into numbers (1 or 0)
The results are displayed in the following image,
Example 3: Combining the BYCOL function with the various Stock functions
We have the following data. It includes scores obtained by each student in Maths, Science, English, History, and Social Science.
Let us consider a special scenario. We need to find:
- The total marks obtained by each student
- Maximum and Minimum marks
- Average marks
Here, we need to combine the BYCOL with the VSTACK function in Excel. It combines multiple arrays vertically into a single array.
The general syntax for the function is as follows:
=VSTACK(array1, [array2], ...)Where array1 and array2 need to be replaced with the first and second arrays to be combined.
Our final formula will be as follows:
=BYCOL(B2:K6, VSTACK(SUM, MAX, MIN, AVERAGE))Where,
- B2:K6 is the array
- VSTACK(SUM, MAX, MIN, AVERAGE) is the formula to find the total, maximum, minimum, and average of the given scores
- SUM, MAX, MIN, and AVERAGE are stock functions
The problem with the above formula is that it generates results for only one student. Refer to the following GIF.
You need to drag the formula for the rest of the students as shown in the following GIF. It requires a few additional efforts, but they are totally worth it.
Takeaway
The BYCOL function offers an excellent way of performing column-based calculations. You can build complex formulas to automate lengthy tasks.
I hope this article taught you all the bells and whistles of the BYCOL function. Please comment below if you are stuck or encounter any particular error while using the BYCOL function. I will answer your questions as soon as possible.
Additional Resources:
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.