G
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}
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}