Need help with counting formula

[bigscientist]

Hi all, here's my question:

I have a bunch of cells that say
PASS
PASS
PASS
FAIL
PASS
PASS

etc etc.

Here's what I need. If there's more than two "FAIL"s in all of the cells, I need my final cell to say "NOT READY." If there are less than two FAILS, I want my final cell to say "READY"

I think it's pretty simple but my brain is completely fried. What's the formula for this? Thank you!

[AliGW]

Administrative Note:

We don't expect you to use words such as urgent or immediate in your thread titles. Please remember that those who help here do so voluntarily and of their own goodwill. They live in many different time zones and many will be out at work right now if they are not asleep on the other side of the globe. This is not a paid 'service': members will help when they are ready and able to do so, and not 'on demand'. Please do not try to put pressure of time on anyone here: if you get the help you need today, that's fine, but you really should not count on it.

[AliGW]

Try this:

=IF(COUNTIF(Range,"Fail")>2,"Not ","")&"Ready"

[bigscientist]

Thanks, its a great start. The range is several different cells. For example, =IF(COUNTIF(H21:H40,P21:P40,L43:L62),"FAIL")>2,"Not Ready","Ready") is not working.

Also, when all the other cells are blank, I want it to be also blank, not "Ready".

How do I fix this?

Sorry about the first title.

[jeffreybrown]

Please post a sample file which will make creating the solution much easier.

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[bigscientist]

Attached is an example what I'm trying to achieve.

I want Cell D24 to say FAIL if 3 or more results say fail.
I want it to say Not Ready if the cells are blank.
And I want it to say Pass if 2 or less say fail.

Hope that makes sense.

[jeffreybrown]

How about in D24 copied over to N and X...

=IF(COUNTA(C2:C21,F2:F21,I2:I21)=0,"Not Ready",LOOKUP(COUNTIF(C2:I21,"FAIL"),{0,3},{"Pass","Fail"}))

[bigscientist]

Works thanks!!!!!

[jeffreybrown]

You are very welcome and thanks for the feedback.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.