Forum Discussion

rcriceinc's avatar
rcriceinc
Copper Contributor
May 20, 2017
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 ...
Formulas & Functions
Show and Tell
  • Logaraj Sekar's avatar
    Logaraj Sekar
    May 21, 2017

    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.

rcriceinc's avatar
rcriceinc
Copper Contributor
May 20, 2017
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).
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    May 20, 2017

    Sorry, you were not very clear.

     

    =AND(OR(A2="Corn",A2="Tomato"),LEFT(A2)="P")

     

    • rcriceinc's avatar
      rcriceinc
      Copper Contributor
      May 20, 2017

      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

      • Logaraj Sekar's avatar
        Logaraj Sekar
        Steel Contributor
        May 20, 2017

        Just see the attached file.

         

        I corrected formula in D column only.

         

        =AND(OR(B2="Corn",B2="Tomato",LEFT(B2,1)="P"))

        Confirm whether i understood your question. Hope, I can solve next one too.