SumProducts on a spreadsheet with a variable row dataset

I import data to a name range with MS query; the number of rows in the range
varies with each month import. I have multiple SumProduct formulas across
the columns.

I know I can use code to replace the bottom of the range reference in each
column's by finding the end of my named reference. However, I'd prefer to
use an offset or cell function instead of having to run a macro with each
query refresh.

Examples of what works
SUMPRODUCT((d5:d1152<>"")/COUNTIF(d5:d1152,d5:d1152&""))


Examples of what doesn't
SUMPRODUCT(("D5:D"&C1<>"")/COUNTIF("D5:D"&C1,"D5:D"&C1&""))
where the c1 contains =rows(MTL)-1
where MTL is my named range
--
Any assistance is appreciated,
Thanks
Nita J. Perez

=SUMPRODUCT((INDIRECT("D5:D"&C1)<>"")/COUNTIF(INDIRECT("D5:D"&C1),INDIRECT("
D5:D"&C1)&""))

--
Regards,
Tom Ogilvy

"NJP" <[email protected]> wrote in message
news:[email protected]...
> I import data to a name range with MS query; the number of rows in the
range
> varies with each month import. I have multiple SumProduct formulas
across
> the columns.
>
> I know I can use code to replace the bottom of the range reference in each
> column's by finding the end of my named reference. However, I'd prefer to
> use an offset or cell function instead of having to run a macro with each
> query refresh.
>
> Examples of what works
> SUMPRODUCT((d5:d1152<>"")/COUNTIF(d5:d1152,d5:d1152&""))
>
>
> Examples of what doesn't
> SUMPRODUCT(("D5:D"&C1<>"")/COUNTIF("D5:D"&C1,"D5:D"&C1&""))
> where the c1 contains =rows(MTL)-1
> where MTL is my named range
> --
> Any assistance is appreciated,
> Thanks
> Nita J. Perez

Tom,

Thanks - That works perfectly - I had tried indirect but missed the
parenthesis prior to the &"".
--
Nita J. Perez


"Tom Ogilvy" wrote:

> =SUMPRODUCT((INDIRECT("D5:D"&C1)<>"")/COUNTIF(INDIRECT("D5:D"&C1),INDIRECT("
> D5:D"&C1)&""))
>
> --
> Regards,
> Tom Ogilvy
>
> "NJP" <[email protected]> wrote in message
> news:[email protected]...
> > I import data to a name range with MS query; the number of rows in the
> range
> > varies with each month import. I have multiple SumProduct formulas
> across
> > the columns.
> >
> > I know I can use code to replace the bottom of the range reference in each
> > column's by finding the end of my named reference. However, I'd prefer to
> > use an offset or cell function instead of having to run a macro with each
> > query refresh.
> >
> > Examples of what works
> > SUMPRODUCT((d5:d1152<>"")/COUNTIF(d5:d1152,d5:d1152&""))
> >
> >
> > Examples of what doesn't
> > SUMPRODUCT(("D5:D"&C1<>"")/COUNTIF("D5:D"&C1,"D5:D"&C1&""))
> > where the c1 contains =rows(MTL)-1
> > where MTL is my named range
> > --
> > Any assistance is appreciated,
> > Thanks
> > Nita J. Perez
>
>
>