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 including creating calculated columns and getting additional help on formulas. We will continue this week with more copilot examples.

While calculated columns are the right answer for some tasks, sometimes the job calls for a single formula that references a table to get a final answer. In this example, I have the results from a questionnaire showing each person’s ID and choices for 5 different options. I would like to count up how many people have selected yes for at least one of the options. I know how to do it by adding another column to my table, but I would like to avoid that. Let’s see if Copilot can help.

A table with columns for ID, Option 1, Option 2, Option 3, Option 4 and Option 5. The ID column contains 3 and 4 digit numerical IDs. The option columns contain Yes or No value with the Yes values highlighted in Green.

We will ask Copilot the following question:

Is there an easy way to count the number of rows in a range in Excel that contain at least one cell with “yes” value without defining a helper column?

Copilot in Excel analyzes the data and the question and returns the response below, explaining how to use the SUMPRODUCT function:

Picture of the Copilot in Excel pane showing the above prompt with a description of the SUMPRODUCT function and how to use it.

Yes, you can use the SUMPRODUCT function to count the number of rows that contain at least one cell with the value “Yes”. Here is a formula that will do this:

=SUMPRODUCT(–(MMULT(–(B3:G12=”Yes”), TRANSPOSE(COLUMN(B3:G12)^0))>0))

This formula checks each row in the range B3:G12 and counts how many rows contain at least one “Yes”.

I don’t know anything about the SUMPRODUCT Or MMULT functions but let’s see if that works. In the Copilot response, there is a button (shown in red below) that will copy the proposed formula to the clipboard.

Excel Copilot Pane with a red square indicating the location of the copy command.

After copying the formula, I select the cell where I would like to put the result and paste it into the cell. The formula returns a value of 6 which I confirmed is working as expected.

Excel workbook showing the data, copilot pane and the results of the formula that was entered.

Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.

Thanks for reading,

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.

​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 including creating calculated columns and getting additional help on formulas. We will continue this week with more copilot examples.
 
While calculated columns are the right answer for some tasks, sometimes the job calls for a single formula that references a table to get a final answer. In this example, I have the results from a questionnaire showing each person’s ID and choices for 5 different options. I would like to count up how many people have selected yes for at least one of the options. I know how to do it by adding another column to my table, but I would like to avoid that. Let’s see if Copilot can help.
 
A table with columns for ID, Option 1, Option 2, Option 3, Option 4 and Option 5. The ID column contains 3 and 4 digit numerical IDs. The option columns contain Yes or No value with the Yes values highlighted in Green.
 
We will ask Copilot the following question:
 
Is there an easy way to count the number of rows in a range in Excel that contain at least one cell with “yes” value without defining a helper column?
 
Copilot in Excel analyzes the data and the question and returns the response below, explaining how to use the SUMPRODUCT function:
 
Picture of the Copilot in Excel pane showing the above prompt with a description of the SUMPRODUCT function and how to use it.
Yes, you can use the SUMPRODUCT function to count the number of rows that contain at least one cell with the value “Yes”. Here is a formula that will do this:
=SUMPRODUCT(–(MMULT(–(B3:G12=”Yes”), TRANSPOSE(COLUMN(B3:G12)^0))>0))
This formula checks each row in the range B3:G12 and counts how many rows contain at least one “Yes”.
 
I don’t know anything about the SUMPRODUCT Or MMULT functions but let’s see if that works. In the Copilot response, there is a button (shown in red below) that will copy the proposed formula to the clipboard.
 
Excel Copilot Pane with a red square indicating the location of the copy command.
 
After copying the formula, I select the cell where I would like to put the result and paste it into the cell. The formula returns a value of 6 which I confirmed is working as expected.
 
Excel workbook showing the data, copilot pane and the results of the formula that was entered.
 
Over the coming weeks I will be sharing more examples of what you can do with Copilot in Excel.
 
Thanks for reading,
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.
   Read More

​