+ Reply to Thread
Results 1 to 14 of 14

How to combine multiple "OR" conditions.

  1. #1
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    41

    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!

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to combine multiple "OR" conditions.

    Where are you using the OR function?

    One way:

    OR(A1={"R","XR","HR","H/R"})
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    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)
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    41

    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.

  5. #5
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: How to combine multiple "OR" conditions.

    Again, PLEASE upload a/the sample workbook/s

  6. #6
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    41

    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

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

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

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,135

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

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  9. #9
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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.

  10. #10
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    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?

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

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

  12. #12
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to combine multiple "OR" conditions.

    Ok, thanks for the confirmation.

  13. #13
    Registered User
    Join Date
    02-16-2013
    Location
    Folsom, CA
    MS-Off Ver
    Excel 2010
    Posts
    41

    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!

  14. #14
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: How to combine multiple "OR" conditions.

    Good deal. Thanks for the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1