View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
 
Posts: 27,285
Default SumProducts on a spreadsheet with a variable row dataset

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

--
Regards,
Tom Ogilvy

"NJP" wrote in message
...
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