Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProducts on a spreadsheet with a variable row dataset
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" 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
SumProducts on a spreadsheet with a variable row dataset
Just wondering, have you considered having the sums at the top of the page instead of the bottom, and then just collecting the sums below? That way, the variable row issue isn't. -- ljsmith ------------------------------------------------------------------------ ljsmith's Profile: http://www.excelforum.com/member.php...o&userid=30531 View this thread: http://www.excelforum.com/showthread...hreadid=547100 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I autodelete rows in a macro on a variable dataset? | Excel Discussion (Misc queries) | |||
Problems with SUMPRODUCTS | Excel Worksheet Functions | |||
SUMPRODUCTS | Excel Worksheet Functions | |||
Converting A Quarterly Dataset to Weekly Dataset | Excel Programming | |||
How Can I paste the dataset on a spreadsheet | Excel Programming |