Multiple OR Statements

[jlkirk]

I know I have seen this somewhere, but can't seem to find it. I have a column of names (B1:B10). What I would like to do is put a formula in C1 that inserts "YES" if three (3) specific names (for example, "John", "Mary", or "Frank") are present in B1:B10, and "NO" if they aren't .
Taks in advance.

 

[pgc01]

Hi

You mean ANY of the 3 names or ALL the 3 names?

 

[jlkirk]

Sorry,
If ANY of the three names are present, insert "YES", and if none of the three are present, insert "NO".
Thanks.

 

[pgc01]

Try:

=IF(SUMPRODUCT(--ISNUMBER(MATCH({"John","Mary","Frank"},B1:B10,0))),"Yes","No")

 

[Colin Legg]

Hi,

To check that all 3 names are present you could use this:

=IF(SUM(--ISNUMBER(MATCH({"John","Mary","Frank"},B1:B10,0)))=3,"Yes","No")

To check that at least one of the names is present you could use this:

=IF(SUM(COUNTIF(B1:B10,{"John","Mary","Frank"})),"Yes","No")

 

[jlkirk]

Thanks.