Count number of rows < Sumproduct
 

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.....

Count number of rows < Sumproduct
 

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

Count number of rows < Sumproduct
 

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.....

Count number of rows < Sumproduct
 

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.....

Count number of rows < Sumproduct
 

=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.....

Count number of rows < Sumproduct
 

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.....

Count number of rows < Sumproduct
 

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)

Count number of rows < Sumproduct
 

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

Count number of rows < Sumproduct
 

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????