Forum Discussion
Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"
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.
I think it needs an array of some kind. I submit an updated excel sample with comments add that may clarify. The basic problem with your sample is that we are not searching for a single cell value but if x instances of an ID occur, and two or more conditions are met, flag the entire ID subset as suspect "T" or "F" identifer. Thanks!
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.
Thank you!!!
I have to give it an actual test on my daily data feed results, but using my simple test file data, looks like both formulas works perfectly, just as intended. I see no reason why it will not work equally well on my current project.
I see the trick now, putting Tomato and Corn in an array. Very neat. I need to work harder on increasing my knowledge of array usage.
Thanks again on this great response.