External named ranges in array formula

[10basetom]

I don't undertand something about Excel...

In file A.xls I have these named ranges:

Visits
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Orders
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$B:$B),1)

In file B.xls I have this formula:

=SUM('A.xls'!Visits)

which returns the correct sum. So far so good. HOWEVER, this array formula returns a #VALUE! error:

=SUM(('A.xls'!Visits=10)*'A.xls'!Orders)

The above formula works if I paste it into A.xls, but it won't work from B.xls. Does anyone have an explanation? Does Excel not support external named ranges in array formulas?

Thanks,
Tom

 

[Aladin Akyurek]

The second SUM formula needs to be confirmed with control+shift+enter instead of the usual enter. BTW, A.xls needs to be open for the formulas to work.

 

[10basetom]

The second SUM formula needs to be confirmed with control+shift+enter instead of the usual enter. BTW, A.xls needs to be open for the formulas to work.

Hi Aladin,

I made sure I entered the array formula with CSE combo. The A.xls file is opened. I still get the same error. Any other ideas?

Thanks,
Tom

 

[Aladin Akyurek]

The second SUM formula needs to be confirmed with control+shift+enter instead of the usual enter. BTW, A.xls needs to be open for the formulas to work.

Hi Aladin,

I made sure I entered the array formula with CSE combo. The A.xls file is opened. I still get the same error. Any other ideas?

Thanks,
Tom

If your ranges include text like labels, you're bound to end up in #VALUE!. Could this be your trouble?

 

[10basetom]

If your ranges include text like labels, you're bound to end up in #VALUE!. Could this be your trouble?

Thank you very much, Aladin -- excluding the column headers from the named ranges solved my problem.

Sorry for responding so late, I was out of the country.

-10bt