# How to combine multiple "OR" conditions.

1. ## How to combine multiple "OR" conditions.

I would like to create an OR statement that has multiple conditions. These would be:
A1="R"
A1="XR"
A1="HR"
A1="H/R"
There would actually be even more conditions.
I was able to do this by listing each individual equation (as in OR(A1="R",A1="XR",A1="HR",A1="H/R"), but I think it must be possible to condense this a bit, and just mention A1 once.

Thanks for any help!  Register To Reply

2. ## Re: How to combine multiple "OR" conditions.

Where are you using the OR function?

One way:

OR(A1={"R","XR","HR","H/R"})  Register To Reply

3. ## Re: How to combine multiple "OR" conditions.

if you have your conditions set up as a list, this MAY be possible using array formula or sumproduct, but would need to see how you got things set up to even try to offer a solution (please upload a sample workbook, NOT a screenshot)  Register To Reply

4. ## Re: How to combine multiple "OR" conditions.

I am using this particular formula in data validation:

=NOT(AND(OR(D4="R",D4="XR",D4="RX",D4="X/R",D4="R/X",D4="HR",D4="RH",D4="H/R",D4="R/H"),\$AF4>8))

This is why I'd like to condense it a bit! I tried using the brackets, but it didn't seem to work.  Register To Reply

5. ## Re: How to combine multiple "OR" conditions.  Register To Reply

6. ## Re: How to combine multiple "OR" conditions.

Sorry, I'm new and I wasn't sure how to attach the workbook. In my sample, the data validation was entered in cell D4 and dragged to the rest of the range. Column AF counts the entries in question, and the data validation is designed to limit the number of these entries to 8. Hope this helps.OR sample.xlsx  Register To Reply

7. ## Re: How to combine multiple "OR" conditions.

Beautiful looking workbook, unfortunately, I can not find any data to work with, nor expected solutions to compare against...I have no idea what you have or expect to see..  Register To Reply

8. ## Re: How to combine multiple "OR" conditions.

hi dzugan, i'm just going to assume your formula is right & you just want to shorten it? otherwise, you would have to tell us what you are trying to do. what is supposed to stop the validation by giving some scenarios. so try this for your validation:
=NOT(AND(OR(D4={"R","XR","RX","X/R","R/X","HR","RH","H/R","R/H"}),\$AF4>8))

and the COUNTIF:
=SUM(COUNTIF(D4:AE4,{"R","HR","RH","H/R","R/H","XR","RX","X/R","R/X"}))  Register To Reply

9. ## Re: How to combine multiple "OR" conditions.

Those brackets { } denote an array constant. You can't use array constants in formulas that define a data validation rule in Excel versions up to at least Excel 2007. I don't have Excel 2010 installed (yet) so I don't know if it'll work in that version.

What you could do is enter the text values in a range of cells and then refer to that range like this:

=NOT(AND(OR(D4=\$E\$4:\$E\$12),\$AF4>8))

Where E4:E12 = list of text values:

R
XR
RX
X/R
R/X
HR
RH
H/R
R/H

Note that if you test that formula in a cell on the worksheet it must be entered as an array formula.

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.

When used as a rule in data validation it will automatically be evaluated as an array formula and no special key entry is needed.  Register To Reply

10. ## Re: How to combine multiple "OR" conditions.

Excel versions up to Excel 2007 will not allow array constants to be used in data validation (and other places).

I have Excel 2010 but it's not installed just yet. I don't know if the array constant rule applies to Excel 2010 and later. Do you know?  Register To Reply

11. ## Re: How to combine multiple "OR" conditions.

No, still can't use constant arrays in Data Validation (or conditional formatting)...Up to 2013 so far..  Register To Reply

12. ## Re: How to combine multiple "OR" conditions.

Ok, thanks for the confirmation.   Register To Reply

13. ## Re: How to combine multiple "OR" conditions.

Thanks to all for helping me with this. Tony was right that arrays can't be used in data validation, but his solution worked well. And thanks to benishiryo, I fixed my countif formula as well. Thanks again!  Register To Reply

14. ## Re: How to combine multiple "OR" conditions.

Good deal. Thanks for the feedback!   Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 