OR as an array formula


Hi all!

I have to check on multiple cells if there is an error, so i make this formula

=IF(OR(O5="missing info",O6="missing info",O7="missing info",O8="missing info",O9="missing info",O10="missing info",O11="missing info",O12="missing info",O13="missing info",O14="missing info"),"missing info",....)

My question is if it's possible to make it like this

=IF(OR(O5:O14="missing info"),"missing info",....)???

I have also the same thing with the AND function



Selected Answer

Have you considered the COUNTIF function?

=IF(COUNTIF(O5:O14, "missing info"), "missing info", "nothing missing")

The formula for AND is very similar. The above returns TRUE if any number of "missing info" is found. If that number is zero, then none were found. Therefore:-

=IF(COUNTIF(O5:O14, "missing info")=0, "no missing info", "something's missing")

dont know if this is what you want:

If there is any error on the formula,"missing info"

=IF(O5="missing info","missing info",IF(COUNTIF($O$5:$O$14,"missing info")>0,"missing info",0))



Under which circumstances would COUNIF return an error? However, if your intention  was to embed the COUNIF in an IFERROR function you may like to review the formula you posted.
Variatus (rep: 813) Jul 27, '17 at 2:45 am
The logic is when one of the item is missing info, then all consider missing info.

In the above requirement , if O5 is missing info or O14 is missing info, then the whole statement will consider missing info.
=IF(OR(O5:O14="missing info"),"missing info",....)???

not sure if this is what he/she wants.
Edward_13 (rep: 2) Jul 27, '17 at 3:42 am
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login