+ Reply to Thread
Results 1 to 3 of 3

Array formula issue

  1. #1
    Registered User
    Join Date
    02-15-2013
    Location
    United States
    MS-Off Ver
    Excel 2010
    Posts
    4

    Array formula issue

    Hi,

    I have been coming across a problem using array formulas and I'm not sure If it's a simple fix or if it's just a limitation in Excel.

    Basically, I am referring to several ranges of cells in a formula. If I refer to a range once like if(AJ2:AM2)="ALL"... the formula will work just fine. However, if I add to the formula like if(and(AJ2:AM2="ALL",AN2:AS2="ALL"... the formula will not recognize one of the ranges at all. So I am being forced to write formulas like if(and(or(AJ2="ALL",AK2="ALL",...etc which works just fine but takes a ton of time and seems like there should be an easier way.

    My questions is why aren't my array formulas working if there are more than one in the same formula? I have to be doing something wrong. An example is below

    {=IF(AND(OR(BC2="10-",BC2="20-",BC2="30-"),(AN2:AS2="HH"),OR(AJ2:AM2="ALL",AJ2:AM2="LHHA")),BC2&"100-"&AT2,"")}

    {=IF(AND(OR(BC2="10-",BC2="20-",BC2="30-"),OR(AN2="HH",AO2="HH",AP2="HH",AQ2="HH",AR2="HH",AS2="HH"),OR(AJ2="ALL",AK2="ALL",AL2="ALL",AM2="ALL",AJ2="LHHA",AK2="LHHA",AL2="LHHA",AM2="LHHA")),BC2&"100-"&AT2,"")}

    In the first formula above,the bold region represents what I'd like to refer to. It's not working so I have to write it like the bold region in the second formula.

    Thank you for any help.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643

    Re: Array formula issue

    Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include BEFORE/AFTER sheets if needed to show the process you're trying to complete or automate. Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    09-11-2009
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    102

    Re: Array formula issue

    hi,
    Try this formula:
    =IF(AND(MAX((BC2={"10-","20-","30-"})*1)=1,COUNTIF(AN2:AS2,"HH")>=1,OR(COUNTIF(AJ2:AM2,"ALL")>=1,COUNTIF(AJ2:AM2,"LHHA")>=1)),BC2&"100-"&AT2,"")
    Hope this helps!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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