Getting help from Copilot in Excel creating a formula to calculate distinct values

Hi everyone, over the last few weeks we have had a series of posts to show you some of the things that are possible to do with Copilot in Excel. This week I have a table of event locations, and I’d like to figure out a formula to count up the distinct locations.

 

Table of event locations with columns for Date and Location

 

 

I would like to find out how many locations are listed, so I ask Copilot:

 

How many distinct locations are there?

 

Copilot in Excel pane with the above prompt and the response below

 

 

I analyzed data in B2:C156, and here's what I found:

Distinct Count of Location
50

Copilot said:

There are 50 distinct locations.

 

Copilot returns an answer of 50, but I would like to have a formula that updates as the list changes. A coworker suggested the formula below, but I don’t understand it. I ask copilot:

 

A coworker gave me this formula to calculate the distinct locations, 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 pane with the above prompt and the response below

 

This formula calculates the number of distinct locations in column C. Here's how it works:

• MATCH(C:C, C:C, 0): This part of the formula creates an array of the positions of each value in column C. The 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 the MATCH function. It returns an array where each element represents the frequency of the corresponding value.
• FREQUENCY(...) > 0: This part of the formula creates a boolean array where each element is TRUE if the corresponding frequency is greater than 0, and FALSE otherwise.
• --(FREQUENCY(...) > 0): The double unary operator (--) converts the boolean array into an array of 1s and 0s, where TRUE becomes 1 and FALSE becomes 0.
• SUM(...): Finally, the SUM function adds up the array of 1s and 0s, giving the total count of distinct locations.

 

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

 

Can you simplify the formula?

 

Copilot in Excel pane with the above prompt and the response below

 

Yes, I can simplify the formula. Here is a more straightforward way to count distinct locations using the UNIQUE and COUNTA functions:

=COUNTA(UNIQUE(Table19[Location]))

 

That is much simpler! I copy the formula from the copilot pane and copy into my workbook. Now when I make changes to the table, the unique location count updates automatically.

 

2 cells with the values “Unique Locations” and the value “50” returned from the formula that was entered, next to the event location table.

 

Here are some additional examples from the last six weeks if you missed them:

• Copilot in Excel examples for the week of August 12th
• Copilot in Excel examples for the week of August 19th
• Copilot in Excel examples for the week of August 26th
• Copilot in Excel examples for the week of September 3rd
• Copilot in Excel examples for the week of September 9th
• Copilot in Excel examples for the week of September 16th

 

Stay tuned,

Microsoft Excel Team

 

*Disclaimer: If you try these types of prompts and they do not work as expected, it is most likely due to our gradual feature rollout process. Please try again in a few weeks.