• 150K Members
• 2,891 Online
• 36.8K Conversations
SOLVED

## Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"

Highlighted
Occasional Contributor

## 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
10 Replies

## Re: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &quot

Hello

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

## RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with "P"

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).

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Sorry, you were not very clear.

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

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

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

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

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.

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

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!

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

Next guess.

`=IF(SUM(COUNTIFS([ID],[@ID],[Condition],{"Tomato";"Corn"}))>1,"T","F")`

Solution

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

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.

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

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.

## Re: RE: Identify ID's meeting multiple criteria (Condition#1: Corn,Tomato - Cndition#2 starts with &

I will test this tomorrow and get back to you. Thanks!