Excel BYROW and BYCOL Helper Functions for LAMBDA

At the end of July 2021, Microsoft released seven new functions that assist with LAMBDA operations.  These functions are (click each to learn more):

(click here for a summary of all seven new functions)

Future posts will cover these new functions, but for this post, I want to focus on the BYCOL and BYROW functions.

The main point of these functions is to consolidate what could be hundreds, perhaps thousands of similar formulas into a single formula.

This will no doubt speed up development but also reduce maintenance time and effort dramatically.

Let’s see how what these new functions are and how they will make you an Excel superstar.

We begin with sales data that is broken down by month (columns) and by employee name (rows).

We have two objectives:

  1. List the months that contain a value that is above the sales target of $1,500 (defined in cell B13).
  2. List the employee name of those that exceeded the sales target.

We want to answer these questions with a single formula for each.

Excel basics course Leila Gharani

Excel Essentials for the Real World - Complete Course

Learn Excel from Scratch or Fill in the Gaps.

Become Confident.

Packed with Challenges & Files (Excel 2016, 2019, Office 365).

GET ACCESS

Objective #1: List months with a sale more than the target

As stated, we want to create a list of months that contain a value that is above the sales target of $1,500 (defined in cell B13).

To perform this operation using a single formula, we will take advantage of the new BYCOL function.

The BYCOL function applies a LAMBDA to each column and returns an array of the results.

More plainly stated: you supply a formula and a range of columns, and the BYCOL function will execute that formula to each column of data separately.  This will produce an array of results; one for each column in the original data range.

Let’s look at an easily understood example

For example, if we were to supply a SUM formula to our data, the sum would be applied to the “Jan” column, then the “Feb” column, and on through to the “Dec” column.  A list of 12 totals would be returned.

The formula (entered in cell B13) would appear as follows:

=BYCOL(B5:M11, LAMBDA(MonthSales, SUM(MonthSales) ) )

We’ll break this formula down in just a moment, but I want to quickly show why this is worth using.

The above formula would yield the same results as creating a separate SUM formula for each of the 12 months.

The advantage is that we only had to create a single formula instead of 12 formulas.

If we need to update the formula, we only need to make a single change to affect the entire list of results.

“But SUM functions are easy, and your formula looks scary and makes me feel uneasy!”

Take a deep breath and sip some warm tea.  The formula is quite simple once you understand the components.  Let’s break the formula down into its pieces.

We begin with the BYCOL function.

=BYCOL(

This function is looking for an array of data by which it will separate into single columns of data.

For our example, we pride the range B5:M11 as the data range.

=BYCOL(B5:M11,

Next, we give the BYCOL function a formula to exercise on every single column of data.

The formula is handled via the LAMBDA function.

Don’t worry if you’re not familiar with the LAMBDA function.  LAMBDA has two arguments: the first is the data to be manipulated and the second is the formula to execute.

We don’t give LAMBDA the data range B5:M11; instead, BYCOL will pass each column, one at a time, into a variable.  We will name our variable “MonthSales”.

=BYCOL(B5:M11, LAMBDA(“MonthSales”,

As each column of sales is passed into the LAMBDA function, the function exercised on each iteration of data is a traditional SUM function.  SUM will use the “MonthSales” variable instead of traditional cell references.

=BYCOL(B5:M11, LAMBDA(“MonthSales”, SUM(MonthSales) ) )

Back to Our Objective

Remember, the objective was to list all months that have a sale greater than the target defined in cell B13.

Start with a BYCOL function and supply the range of data to be processed.

=BYCOL(B5:M11,

Next, create a LAMBDA function that uses a variable named “mycol”.  The variable name can be anything you wish.  It is there to accept the input supplied at each iteration of the BYCOL function.

=BYCOL(B5:M11, LAMBDA(mycol,

The formula we will exercise against each column of data will be to count the number of sales that exceed the value in cell B13.

=BYCOL(B5:M11, LAMBDA(mycol, COUNTIF(mycol, “>” & B13) ) )

NOTE:  We must place the greater-than sign in quotes since we a concatenating text with a cell reference.

Any column that has a sale greater than the value in cell B13 will be counted.

Since we were trying to list the months, not the number of instances per month, we will now place this formula inside a FILTER function to only show months (cells B4:M4) that correspond to a value greater than 0 (zero) in the BYCOL results.

=FILTER(B4:M4, BYCOL(B5:M11, LAMBDA(mycol, COUNTIF(mycol, “>” & B13) ) ) > 0)

If we want the list of months to occupy a single column instead of a single row, we just place the entire existing formula inside a TRANSPOSE function.

=TRANSPOSE(FILTER(B4:M4, BYCOL(B5:M11, LAMBDA(mycol, COUNTIF(mycol, “>” & B13) ) ) > 0) )

Objective #2: List users with a sale more than the target

Now that we have accomplished the first objective, it will be a simple matter to determine the users with a sale greater than the sales target.

We start in the same way as before but with a BYROW function instead of a BYCOL function.

=BYROW(

The array of data that we will iterate thru one row at a time is the same array used in the first objective: B5:M11.

=BYROW(B5:M11,

We feed the BYROW function a LAMBDA function that uses a variable named “myrow”.

=BYROW(B5:M11, LAMBDA(myrow,

The calculation is the same as the first objective, count the number of times a sale greater than the value in cell B13 occurs.

=BYROW(B5:M11, LAMBDA(myrow, COUNTIF(myrow, “>” & B13) ) )

The results show us how many times an employee had a sale that exceeded the target.

Next, we incorporate a FILTER function to show only names (cells A5:A11) where the counted value is greater than 0 (zero).

=FILTER(A5:A11, BYROW(B5:M11, LAMBDA(myrow, COUNTIF(myrow, “>” & B13) ) ) > 0)

Practice Workbook

Feel free to Download the Workbook HERE.

Excel Download Practice file

Excel Dynamic Arrays Course Leila Gharani

Master Excel Functions in Office 365 & Office 2021 - Complete Course

Excel has Changed Forever! DON'T MISS OUT!

GET ACCESS