I hope someone can help me with following issue:

I am using a sumproduct formula --- =SUMPRODUCT(($A$2:$A$12569="101")*($B$2:
$B$12569="6")*($E$2:$E$12569={4;5;6})*$C$2:$C$1256 9)

As you can see It starts with row number 2 and in this case ends at 12569. My
problem is that the area E2:E12569 comes from a query from another program.
Next time I run this query it may end up with another row number (ex. 11876
or 33215) Can i make my sumproduct formula count the number of rows and
automatically insert this number in the formula???

Thank you in advance.....

You can use dynamic ranges which are described on 'this site'
(http://www.contextures.com/xlNames01.html#Dynamic)


--
Pecoflyer

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows file
upload -faster and better answers
------------------------------------------------------------------------
Pecoflyer's Profile: http://www.thecodecage.com/forumz/member.php?userid=14
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=47945

Try this:
=SUMPRODUCT((INDIRECT("$A$2:$A$"&COUNT(C:C)+1)="10 1")*(INDIRECT("$B$2:$B$"&COUNT(C:C)+1)="6")*(INDIR ECT("$E$2:$E$"&COUNT(C:C)+1)={4,5,6})*INDIRECT("$C $2:$C$"&COUNT(C:C)+1))

Regards,
Stefi

€žmicro1000€� ezt Ã*rta:

I hope someone can help me with following issue:

I am using a sumproduct formula --- =SUMPRODUCT(($A$2:$A$12569="101")*($B$2:
$B$12569="6")*($E$2:$E$12569={4;5;6})*$C$2:$C$1256 9)

As you can see It starts with row number 2 and in this case ends at 12569. My
problem is that the area E2:E12569 comes from a query from another program.
Next time I run this query it may end up with another row number (ex. 11876
or 33215) Can i make my sumproduct formula count the number of rows and
automatically insert this number in the formula???

Thank you in advance.....

You could simply use the smallest max expected extent, say row 50000:
=SUMPRODUCT((A2:A50000="101")*(B2:B50000="6")*(E2: E50000={4;5;6})*C2:C50000)

Another way, assuming col E will determine the last row of data, and that
data in E2 down will be contiguous nums right down (ie no blank cells
in-between), then this:
=SUMPRODUCT((A2:INDEX(A2:A50000,COUNT(E2:E50000))= "101")*(B2:INDEX(B2:B50000,COUNT(E2:E50000))=" 6")
*(E2:INDEX(E2:E50000,COUNT(E2:E50000))={4;5;6})*C2 :INDEX(C2:C50000,COUNT(E2:E50000)))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"micro1000" <u48676@uwe wrote in message news:8fdda63bd2cb0@uwe...
I hope someone can help me with following issue:

I am using a sumproduct formula ---
=SUMPRODUCT(($A$2:$A$12569="101")*($B$2:
$B$12569="6")*($E$2:$E$12569={4;5;6})*$C$2:$C$1256 9)

As you can see It starts with row number 2 and in this case ends at 12569.
My
problem is that the area E2:E12569 comes from a query from another
program.
Next time I run this query it may end up with another row number (ex.
11876
or 33215) Can i make my sumproduct formula count the number of rows and
automatically insert this number in the formula???

Thank you in advance.....

=SUMPRODUCT(($A$2:INDEX($A:$A,COUNTA($A:$A))="101" )*($B$2:INDEX($B:$B,COUNTA($A:$A))="6")
*($E$2:INDEX($E:$E,COUNTA($A:$A))={4,5,6})*$C$2:IN DEX($C:$C,COUNTA($A:$A)))

--
__________________________________
HTH

Bob

"micro1000" <u48676@uwe wrote in message news:8fdda63bd2cb0@uwe...
I hope someone can help me with following issue:

I am using a sumproduct formula ---
=SUMPRODUCT(($A$2:$A$12569="101")*($B$2:
$B$12569="6")*($E$2:$E$12569={4;5;6})*$C$2:$C$1256 9)

As you can see It starts with row number 2 and in this case ends at 12569.
My
problem is that the area E2:E12569 comes from a query from another
program.
Next time I run this query it may end up with another row number (ex.
11876
or 33215) Can i make my sumproduct formula count the number of rows and
automatically insert this number in the formula???

Thank you in advance.....

HI Max, I succeeded using your formula with index. I only had to change a ,
to ;

But you have just made my day.

Thank you very much.... :0)

Max wrote:
You could simply use the smallest max expected extent, say row 50000:
=SUMPRODUCT((A2:A50000="101")*(B2:B50000="6")*(E2 :E50000={4;5;6})*C2:C50000)

Another way, assuming col E will determine the last row of data, and that
data in E2 down will be contiguous nums right down (ie no blank cells
in-between), then this:
=SUMPRODUCT((A2:INDEX(A2:A50000,COUNT(E2:E50000)) ="101")*(B2:INDEX(B2:B50000,COUNT(E2:E50000))=" 6")
*(E2:INDEX(E2:E50000,COUNT(E2:E50000))={4;5;6})*C 2:INDEX(C2:C50000,COUNT(E2:E50000)))
I hope someone can help me with following issue:

[quoted text clipped - 12 lines]

Thank you in advance.....

Welcome, glad you got it up there
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik
---
"micro1000" <u48676@uwe wrote in message news:8fde9d1479e74@uwe...
HI Max, I succeeded using your formula with index. I only had to change a
,
to ;

But you have just made my day.

Thank you very much.... :0)

Hi Max,

do you have any idea how to make the index not count blank cells as 0????

Max wrote:
Welcome, glad you got it up there
HI Max, I succeeded using your formula with index. I only had to change a
,
[quoted text clipped - 3 lines]

Thank you very much.... :

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...tions/200901/1

Pl put in a fresh new posting, with more detail / examples.
You'd get better exposure for your query to ALL responders
(This thread is long closed)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,500 Files:370 Subscribers:66
xdemechanik
---
"micro1000 via OfficeKB.com" <u48676@uwe wrote in message
news:9068f06c18bb5@uwe...
Hi Max,

do you have any idea how to make the index not count blank cells as 0????