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.
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.
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”.
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.
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.
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) )