Function to check list for specific conditions and return an answe

T

tanya

I have a list of five cells which may contain any one of the following
numbers 017, 030,0. In another cell i want to do the following:
If all five cells only contain 017 or 0 then return a value of 017
If all five cells only contain 030 or 0 then return a value of 030
If all five cells contain a combination of all three numbers the cell to
show "Check"
 
O

Ola

Try this
=IF(COUNTIF(A1:A5,"017")+COUNTIF(A1:A5,0)=5,"017",IF(COUNTIF(A1:A5,"030")+COUNTIF(A1:A5,0)=5,"030",IF(COUNTIF(A1:A5,"017")+COUNTIF(A1:A5,"030")+COUNTIF(A1:A5,0)=5,"Check","-")))

Ola
 
B

Bob Phillips

Hi Tanya,

I think this does it

=IF(OR(COUNTIF(A1:A5,"017")=5,COUNTIF(A1:A5,"0")=5),"017",IF(COUNTIF(A1:A5,"
030")=5,"030","Check"))
 
Top