Formula troubles

[egeorge4]

I am having trouble getting this formula to work, can anyone help m
out?

This is what I have:
=IF(Z4:AC4,AH4:AJ4={FAIL},"YES","NO")

I am trying to show: If Z4 thru AC4 and AH4 thru AJ4 are equal t
"FAIL" display "YES" otherwise display "NO".

Any help is much appreciated! Biff really hooked me up last time I ha
a formula roadblock...Biff, are you out there?

 

[Ron Coderre]

Try this:

=IF((COUNTIF(Z4:AC4,"Fail")+COUNTIF(AH4:AJ4,"Fail"))=7,"YES","NO")

Does that help?

Regards,
Ro

 

[Ron Coderre]

Try this:

=IF((COUNTIF(Z4:AC4,"Fail")+COUNTIF(AH4:AJ4,"Fail"))=7,"YES","NO")

Does that help?

Regards,
Ron

 

[egeorge4]

No Ron, that didnt work. It just displays "NO" regardless of the text in
the specified fields.

I should have mentioned that there are three possible answers that will
display in the specified fields:

PASS, FAIL, or N/A

Is that causing problems for the formula you sent?

 

[Ron Coderre]

Check these:

Are the cell references correct?
Do the cell contents have any extra characters (spaces befor or after,
etc)?

Using:
A1: =IF((COUNTIF(Z4:AC4,"Fail")+COUNTIF(AH4:AJ4,"Fail"
))=7,"YES","NO")

In my test, when I put the word "Fail" (without the quotes, of course)
in cells Z4:AC4 and AH4:AJ4, the count equalled 7 and the formula
returned YES. If any other values or blanks are in those cells, it
returns NO.

Regards,
Ron

 

[Niek Otten]

Ron's solution works fine for me. Are you sure there aren't any spaces in
the texts?

 

[egeorge4]

OK, I figured it out. You were setting the results to base on a fail in
all 7 fields, but I was looking for a fail in ANY of the cells to
trigger a "YES". I removed the "=7" and it looks like it works! Thanks
a million!