Function to check list for specific conditions and return an answe

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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"
 
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
 
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"))
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

conditional formatting 4
what is the formula for returning the amount to amount in words 1
Finding Text within an Array 2
Return specific values based on multiple crireria 2
Conditional Formatting 4
conditional formatting for more than 3 conditions 3
counting identical items in a column and returning the occurrences 3
Excel Need Countifs Formula Help 0

Back
Top