Forum Discussion
Solved
Perform a index match groupping data and with a condition
I am going around in circles and I am looking for help. the situation is:
Have the following data:
| Data | Otro |
| A | SI |
| A | No |
| A | No |
| A | No |
| B | si |
| B | si |
| B | si |
| C | No |
| C | si |
| D | no |
| D | no |
and having the following column
| Find | Result |
| A | |
| B | |
| C | |
| D |
The final result should B
| Find | Result |
| A | No |
| B | Si |
| C | No |
| D | No |
The idea is looking for the values in the column "Find", lets get the first values as example "A". Look for A in the column Data, and if all the values in column "Otro" are "si", then the result will be "Si", if any of the values for A is no, then the result will be know
How can I do it?
thansk a lot for your help
The formula in E2 is
=IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"no")=0,"Si","No")
You'll have to adjust the ranges for your layout.
The formula in E2 is
=IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"no")=0,"Si","No")
You'll have to adjust the ranges for your layout.
HansVogelaar It works great but I have another question, how could I adapt the formula in the event that I have data in column D that it is not in column A? For those cases I will like to return the value "Not in list"
=IF(COUNTIF($A$2:$A$12,D2)=0,"Not in list",IF(COUNTIFS($A$2:$A$12,D2,$B$2:$B$12,"no")=0,"Si","No"))
