Forum Discussion
Solved
Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"
This is probably easier than I think but at 4:30 in the morning not thinking clearly.
| Test#1 = ID = "Corn" and "Tomato" Test #2 = ID = (starts with "P") Both test conditions should return a "T" or "F" result (Boolean is OK) | |
| Condition | ID |
| Apple | 000001 |
| Berry | 000001 |
| Coconut | 000001 |
| Corn | 000001 |
| Date | 000001 |
| Melon | 000001 |
| Orange | 000001 |
| Peach | 000001 |
| Pear | 000001 |
| Tomato | 000001 |
| Apple | 000002 |
| Berry | 000002 |
| Coconut | 000002 |
| Date | 000002 |
| Melon | 000002 |
| Orange | 000002 |
| Peach | 000002 |
Hi, rcriceinc
For Test Condition 1, apply Detlef_Lewin 's formula
=IF(SUM(COUNTIFS([ID],[@ID],[Condition],{"Tomato";"Corn"}))>1,"T","F")For Test Condition 2, use this.
=IF(COUNTIFS([ID],[@ID],[Condition],"P*")>=1,"T","F")
Hope both will solve your problem.
10 Replies
- Does not work, remember, the ID values occur multiple times with different conditions and I want to identify ID's only where both conditions are true, not ID's where either condition is =T. Same thing for the second one (All = "P" start).
Sorry, you were not very clear.
=AND(OR(A2="Corn",A2="Tomato"),LEFT(A2)="P")
Don't know how to be more clear. The "And" in test #1 should have been clear. Both conditions must exist before a "T" result is returned. If both conditions are not true, then return "F".
Test#1 = ID = "Corn" and "Tomato"
test condition should return a "T" or "F" result (Boolean is OK)Treat test#2 seperately using the same data.
EX:
ID#1 occurs multiple times with multiple conditions.
Return "F" unless an ID has instances with both Corn and Tomato
Hello
=OR(A2="Corn",A2="Tomato") =LEFT(A2)="P"
