The Excel BYROW function lets you perform row-wise calculations using a custom LAMBDA function. It returns one result per row.
For example, suppose that you are a Sales Manager. You wish to find the total sales generated by each team member in the given quarter. Refer to the following image,
Our formula would be as follows:
=BYROW(B2:D11, LAMBDA(sales, SUM(sales)))Where,
- B2:D11 is the range or array
- LAMBDA(sales, SUM(sales)) is the LAMBDA calculation to be applied to each row of the given array
- SUM(sales) is the formula to add numeric values in the given row
The results are displayed in the following GIF.
Syntax
The BYROW is a simple logical function with the following syntax. It is used to apply a custom LAMBDA function to each row in the given array.
=BYROW(array, [function])Where,
- array argument needs to be replaced with the range or array to which we wish to apply the LAMBDA calculation row-wise
- function argument needs to be replaced with the LAMBDA calculation
Important Notes:
- The function displays results across a vertical 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 row during calculation
How to use the BYROW Function in Excel
Download the example spreadsheet used to demonstrate the BYROW function in the upcoming section. Practice is the key to success!
Example 1: BYROW 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 in the first column. Then, we have their scores in Math, Science, English, History, and Social Studies in columns B, C, D, E, and F. Now, let us use the BYROW function to calculate their total marks within a few seconds.
Here are the steps,
- Select the cell H2
- Type =BYROW
- Select the first option from the popup
- Specify the array B2:F11
- 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 BYROW function using )
- Hit the Enter key
Our final formula is as follows,
=BYROW(B2:F11, LAMBDA(scores, SUM(scores)))Where,
- B2:F11 is an array including scores obtained by each student
- LAMBDA(scores, SUM(scores)) is the LAMBDA calculation to be performed on each row
- 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 BYROW function, depending on how you define the function (calculation) to be applied to each row of the given array.
METHOD #1: Custom LAMBDA Calculation
This method requires you to define a custom LAMBDA function within the BYROW 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 BYROW function, I can simply write:
Yes, it is that easy. All you need to do is write SUM, and the BYROW function will add all the values available across each row 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 H2
- Type =BYROW
- Select the first option from the popup
- Specify the array B2:F11
- 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 BYROW function using )
- Press the Enter key
Our final formula is as follows,
=BYROW(B2:F11, LAMBDA(score, SUM(--(score < 40))))Where,
- B2:F11 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 BYROW 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 BYROW with the HSTACK function in Excel. It combines multiple arrays horizontally into a single array.
The general syntax for the function is as follows:
=HSTACK(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:
=REDUCE(0, B2:B11, LAMBDA(count, order, IF(order>=100, count +1, count)))Where,
- B2:F11 is the array
- HSTACK(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 BYROW function offers an excellent way of performing row-based calculations. You can build complex formulas to automate lengthy tasks.
I hope this article taught you all the bells and whistles of the BYROW function. Please comment below if you are stuck or encounter any particular error while using the BYROW 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.