I have this table:
| Vitamin | Apple | Lemon | Orange |
|---|---|---|---|
| Selected | X | X | |
| A | Yes | Yes | Yes |
| C | Yes | Yes | Yes |
| X | Yes | ||
| Y | Yes | ||
| Z | Yes |
And I want to create something like this:
| Vitamin | Selected |
|---|---|
| A | Yes |
| C | Yes |
| X | Yes |
| Y | |
| Z | Yes |
The idea is that the output should be "Yes" if the value is "Yes" in any column that is marked as selected. So in the example above Vitamin Y is empty (or "0", or "No") because no selected fruit has it.
Right now, the "best" working solution so far I have is:
=IF(
IF(B$2="X",IF(B3="Yes",1,0),0)
IF(C$2="X",IF(C3="Yes",1,0),0)
IF(D$2="X",IF(D3="Yes",1,0),0)
>0,"Yes","")
The problem is that whenever I need to add new columns or rows, I will need to update the formula accordingly.
Is there a way to calculate it?
I have some freedom to change the structure if needed.
It would be great if by adding more rows to the first table, said columns are automatically added to the second table, but this is not a requirement.
I am OK with the output being a number so wrap it with an IF, or I can simply format it. But the input will be "Yes".
Limitation: this must work in the online version of Excel.
CodePudding user response：
Give the following a try:
Formula in F3:
=IF(SUMPRODUCT((B$2:D$2="X")*(B3:D3="Yes")),"Yes","")