G

#### GTVT06

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}