# Array formula issue

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

3. ## 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!

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

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