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 5x 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.