We begin with sales data that is broken down by month (columns) and by employee name (rows).
We have two objectives:
- List the months that contain a value that is above the sales target of $1,500 (defined in cell B13).
- List the employee name of those that exceeded the sales target.
We want to answer these questions with a single formula for each.
Featured Course
Excel Essentials for the Real World
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.
Featured Course
Master NEW Excel Functions in Office 365 & Office 2021
Leila Gharani
I'm a 6x Microsoft MVP with over 15 years of experience implementing and professionals on Management Information Systems of different sizes and nature.
My background is Masters in Economics, Economist, Consultant, Oracle HFM Accounting Systems Expert, SAP BW Project Manager. My passion is teaching, experimenting and sharing. I am also addicted to learning and enjoy taking online courses on a variety of topics.
