SUMPRODUCT to calculate data from a closed workbook

[GTVT06]

Hello,
Is any one able to help me figure out what I'm doing wrong?
When I do a sumproduct on a closed workbook for the exact number of
rows it works, but when I re-write the formula to include even one
additional row (1 row of blank data) it will give me a #REF! error
unless the workbook is open. The reason why I want to include blank
rows is because the rows can vary from day to day, and the sumproduct
cant include a N:N type of selection.
Here's some examples.

I currently have exactly 1086 rows of data, so this formula works with
the workbook closed
{=SUMPRODUCT(--('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$R$2:$R
$1086="KC")*('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$M$2:$M
$1086="ABS ")*('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$N$2:$N
$1086<>""),--('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$N$2:$N$1086))/
60}

But when I add additional rows to the formula I get a #REF! error. In
the example below it's the exact same formula as above but instead of
the data ranges ending on row 1086 I have them ending on row 2086. I'm
also entering them as array formulas.
{=SUMPRODUCT(--('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$R$2:$R
$2086="KC")*('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$M$2:$M
$2086="ABS ")*('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$N$2:$N
$2086<>""),--('C:\LongPath\[SpreadSheet.csv]SpreadSheet!$N$2:$N$2086))/
60}

 

[Roger Govier]

Hi

You need to go to Edit>Links>Open source then close your source book.
Then save the destination book and the links will be fine

You are missing a closing quote between Spreadsheet and ! in each part
of the formula.
The formula does not need to be array entered.

 

[GTVT06]

But you would also have to Edit>Links>Open source every time any
changes are made and I was trying to prevent having to open any other
external workbooks. Edit>Links>Open source will also work for Sum(if's
but I thought Sumproduct was supposed to update and calculate from
external workbooks without having to open them in any type of way. I
was thinking it was something else since Sumproduct works just fine as
long as I have the exact number of rows in the formula that include
data.

 

[Roger Govier]

Hi

No, I am saying set your range with 1000 extra rows.
Then choose Edit links, open source, close source, save destination
file.
You won't have to go through the same procedure, unless you change the
number of rows again in your formula.

 

[GTVT06]

Got it. Thanks, I figured out it wasn't working in csv format so I had
to save as xls and it worked like a charm. Thanks for your help!