BritsBlitz
New Member
- Joined
- Jan 10, 2014
- Messages
- 34
- Office Version
- 365
- Platform
- Windows
Hi. I'm looking for an Excel formula that will do the following based on the table below:
I need a formula that will look at all the lines of Apples and if any of the lines has a "Yes" in Column B, then it needs to return a TRUE result. The same for the lines with Oranges; the formula needs to look at all the lines with Oranges and if any of them has a "Yes" in Column B, then it needs to return a TRUE result. Same for Peaches, etc.
Here's he part I'm struggling with: I cannot used a pre-defined range for Apples, Oranges and Peaches because sometimes there will be 2 lines with Apples and sometimes there will be 10 lines with Apples so the formula first needs to look at all the lines in the table, then identify the lines with "Apples" and then look within each line for a "Yes". It doesn't matter how many "Yes" entries there are within all the lines of Apples, as long as there is at least one, it should return a TRUE result.
I need to use one formula to search the entire table with the entries in Column A being my "triggers". For instance, if I want results based on Apples, the formula should return the result from above for the lines with Apples. If the trigger is Oranges, then the result should be based on only the lines with Oranges, etc.
Column A | Column B |
Apples | No |
Apples | Yes |
Oranges | No |
Oranges | No |
Oranges | Yes |
Peaches | No |
Peaches | No |
I need a formula that will look at all the lines of Apples and if any of the lines has a "Yes" in Column B, then it needs to return a TRUE result. The same for the lines with Oranges; the formula needs to look at all the lines with Oranges and if any of them has a "Yes" in Column B, then it needs to return a TRUE result. Same for Peaches, etc.
Here's he part I'm struggling with: I cannot used a pre-defined range for Apples, Oranges and Peaches because sometimes there will be 2 lines with Apples and sometimes there will be 10 lines with Apples so the formula first needs to look at all the lines in the table, then identify the lines with "Apples" and then look within each line for a "Yes". It doesn't matter how many "Yes" entries there are within all the lines of Apples, as long as there is at least one, it should return a TRUE result.
I need to use one formula to search the entire table with the entries in Column A being my "triggers". For instance, if I want results based on Apples, the formula should return the result from above for the lines with Apples. If the trigger is Oranges, then the result should be based on only the lines with Oranges, etc.