See what’s possible with Copilot in Excel (part 7)

Hello everyone, over the past few weeks we have been series of posts Let me show you a few things you can do with Copilot in Excel. This week, we’ll show you how Copilot in Excel can help you create formulas to calculate unique values.

In this example we have Given a table of event locations, I would like to figure out a formula to calculate each location.

Event location table with date and location columns

Let’s find out how many locations are listed based on the returned values.

How many different locations are there?

Copilot in Excel window with prompt above and response below.

The analyzed data for B2:C156 returns:

The co-pilot said:

There are 50 different locations.

Copilot returns the answer of 50, but I’d like to have a formula that updates when the list changes. My colleague suggested the formula below, but I don’t understand it. I ask my co-pilot:

A colleague gave me the following formula to calculate distinct positions: Can you tell me how it works? =SUM(–(FREQUENCY(MATCH(C:C, C:C, 0), MATCH(C:C, C:C) , 0)) > 0))

Copilot in Excel window with prompt above and response below.

This formula calculates the number of individual positions in column C. Here’s how it works:

• MATCH(C:C, C:C, 0): This part of the formula creates an array of positions for each value in column C. 0 indicates an exact match.
• FREQUENCY(MATCH(C:C, C:C, 0), MATCH(C:C, C:C, 0)): The FREQUENCY function is used to count the number of times each value appears in the array created by MATCH. . function. Returns an array where each element represents the frequency of that value.
• FREQUENCY(…) > 0: This part of the formula creates a boolean array whose each element is TRUE if its frequency is greater than 0 and FALSE otherwise.
• –(FREQUENCY(…) > 0): The double unary operator (–) converts a boolean array to an array of 1’s and 0’s. Here TRUE becomes 1 and FALSE becomes 0.
• SUM(…): Finally, the SUM function adds an array of 1’s and 0’s to give the total number of unique positions.

I understand the formula, but it seems too complicated. I’d like to see if Copilot can do better. I ask:

Can you simplify the formula?

Copilot in Excel window with prompt above and response below.

Yes, you can simplify the formula. Here’s a simpler way to count unique locations using the UNIQUE and COUNTA functions:

=COUNTA(Unique(Table 19[Location]))

It’s much simpler! Copy the formula from the Copilot window and copy it into your workbook. Now, when you make changes to the table, the unique location count is automatically updated.

Two cells with the values ​​’Unique Locations’ and ’50’ returned from the formula entered next to the Event Locations table.

If you missed some additional examples from the past six weeks, here are a few more:

Stay tuned.

Microsoft Excel Team

*Disclaimer: If you’ve tried these types of messages and they don’t work as expected, it’s most likely due to our gradual feature rollout process. Please try again in a few weeks.