Indirect Function in an array formula

[Eagle96]

Hi

I am using an array function to sum values if any one of a criteria in my criteria array is a match (note it's an or not and AND requirement). The purpose being to roll up the values of multiple account identifiers into a more simplified list of accounts.

The array =SUMPRODUCT(IFERROR(IF(MATCH([testfile.xlsx]sheet1!$A$11:$A$400,AU18:AY18,0)>0,1,0),0),[testfile.xlsx]sheet1!$C$11:$C$400)
correctly calculates the values I need (AU:AY contains the account identifiers that form my sum criteria, column A in my other workbook contains the full list of account IDs and column C the values I want to sum).

However ideally I would like it to reference a dynamic range so that the user can easily just update their file and worksheet names. I know they will throw their arms in the air as soon as they hit the "you can't change an array" message otherwise! So I have been trying without success to use the indirect function. I've attempted it longhand and get ref errors so I also tried it just referencing single cells which have a text formula containing the name.
i.e. [TABLE="width: 118"]
<tbody>[TR]
[TD][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
BA7 = [testfile.xlxs]sheet1!$A11:$A400
& BA8 = [testfile.xlxs]sheet1!$C11:$C400

& the formula I'm using in my column (after pressing ctrl shift enter) is
=SUMPRODUCT(IFERROR(IF(MATCH(INDIRECT($BA$7),AU18:AY18,0)>0,1,0),0),INDIRECT($BA$8))
[TABLE="width: 72"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
The values being returned though are all based on AU18:AY18 (and not AU19:AY19, AU20:AY20 etc. as you move down the column). It seems like the indirect is locking in this row also when I only want the indirect to apply to the reference to the other workbook.

Please help! What have I missed?

Alternatively is there a more elegant solution? I was initially using loops in vba to cycle from AU to AY and add the results and then move down the column, however I couldn't get the column to not be a sum of all the preceding columns so gave up on that method temporarily..

Thanks in advance!!

 

[DILIPandey]

Hi Eagle,

Suggest you to read about the limitations of Indirect function:-

https://support.office.com/en-us/article/INDIRECT-function-474b3a3a-8a26-4f44-b491-92b6306fa261

relevant extract below:-
If ref_text refers to another workbook (an external reference), the other workbook must be open. If the source workbook is not open, INDIRECT returns the #REF! error value.

http://www.pcreview.co.uk/threads/offset-indirect-match-function-limitation-on-linked-worksheets.1772186/

Regards,
DILIPandey

 

[Eagle96]

Thanks Dili,

I did try to do a fair bit of reading on possible limitations of indirect before I posted. My other workbook is definitely open - sorry, I should have made this clearer. Any other ideas for what is going wrong?

 

[FormR]

BA7 = [testfile.xlxs]sheet1!$A11:$A400
& BA8 = [testfile.xlxs]sheet1!$C11:$C400

Hi, shouldn't these be .xlsx- I also think your formula can be changed to the normally entered:

=SUMPRODUCT(--(ISNUMBER(MATCH(INDIRECT($BA$7),AU18:AY18,0))),INDIRECT($BA$8))

 

[Eagle96]

Thanks so much FormR!! That's a much better solution.