Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

OR as an array formula

0

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

excel
MS Office Version: 2013
posted by: JAExcel (rep: 10) on Wed Jul 26, 2017 at 6:12 pm
Answer
Discuss

Answers

0
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")
posted by: Variatus (rep: 4889) on Wed Jul 26, 2017 at 10:34 pm
Discuss
-1

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))

posted by: Edward_13 (rep: 2) on Thu Jul 27, 2017 at 12:04 am
Discuss

Discussion

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: 4889) 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