Count number of rows <> Sumproduct

[micro1000]

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$12569)

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

 

[Pecoflyer]

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

--
Pecoflye

Cheers -
*'Membership is free' (http://www.thecodecage.com)* & allows fil
upload ->faster and better answer

 

[Stefi]

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

Regards,
Stefi

„micro1000†ezt írta:

 

[Max]

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

 

[Bob Phillips]

=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:INDEX($C:$C,COUNTA($A:$A)))

 

[micro1000]

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)

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

I hope someone can help me with following issue:

[quoted text clipped - 12 lines]

Thank you in advance.....

 

[Max]

Welcome, glad you got it up there
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[micro1000 via OfficeKB.com]

Hi Max,

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

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

 

[Max]

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