OR statement with range of cells

  • Thread starter Thread starter ArtySin
  • Start date Start date
A

ArtySin

Hi, I have a formula which work well for 4 cells with the same condition to
give the result in another cell:
=IF(OR(J245="Fail",J246="Fail",J247="Fail",J248="Fail"),"F","P")
I now however, need to do the same thing for a range of cells that is more
than 2
0 in total. I've tried naming a range but that comes back with #VALUE! as
the result. Anone any idea how I can do this at all?
Many thanks
 
You could try something like this:

=IF(COUNTIF(J245:J265,"Fail")>0,"F","P")

If any of the cells in the range J245:J265 have Fail, the formula will
return F.

Hope this helps.

Pete
 
As long as the range of cells in question is a contiguous block I would use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.
 
As long as the range of cells in question is a contiguous block I would use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Thanks V much guys now works perfectly :-)
 
You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


As long as the range of cells in question is a contiguous block I would
use
Pete's suggestion.

You could still use OR but it would have to be array entered** :

=IF(OR(J245:J265="Fail"),"F","P")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the
SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP






- Show quoted text -
Click to expand...

Thanks V much guys now works perfectly :-)
 
Hi, I have a formula which work well for 4 cells with the same condition to
give the result in another cell:
=IF(OR(J245="Fail",J246="Fail",J247="Fail",J248="Fail"),"F","P")
I now however, need to do the same thing for a range of cells that is more
than 2
0 in total. I've tried naming a range but that comes back with #VALUE! as
the result. Anone any idea how I can do this at all?
Many thanks
Click to expand...

Another one, just for fun:
=IFERROR(REPT("F",SIGN(MATCH("Fail",J245:J299,0))),"P")

Regards,
Bernd
 
Back
Top