Hi

I have following sumproduct:

=SUMPRODUCT(($A$2:$A$9487="101")*($B$2:$B$9487="0" )*($E$2:$E$9487=
{4;5;6})*$C$2:$C$9487)

My problem is that my sheet always starts at row #2 but the numbers of
rows change from time to time - so I need excel to count the numbers
of rows and automatically place it where 9487 is placed today.

Can someone help me?

Thank you ind advance

Mikael

Why not super-size? In place of:
$A$2:$A$9487
use:
$A$2:$A$65535


etc.
--
Gary''s Student - gsnu2007k


" wrote:

Hi

I have following sumproduct:

=SUMPRODUCT(($A$2:$A$9487="101")*($B$2:$B$9487="0" )*($E$2:$E$9487=
{4;5;6})*$C$2:$C$9487)

My problem is that my sheet always starts at row #2 but the numbers of
rows change from time to time - so I need excel to count the numbers
of rows and automatically place it where 9487 is placed today.

Can someone help me?

Thank you ind advance

Mikael

Hi,

Highlight A2:E9487 and go to Data List Create List. A blue border
around the range indicates that the range has been converted to a list. Now
the range becomes auto expanding in nature I.e. when you add row 9488, the
sumproduct formula would automatically extend to row 9488.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
Hi

I have following sumproduct:

=SUMPRODUCT(($A$2:$A$9487="101")*($B$2:$B$9487="0" )*($E$2:$E$9487=
{4;5;6})*$C$2:$C$9487)

My problem is that my sheet always starts at row #2 but the numbers of
rows change from time to time - so I need excel to count the numbers
of rows and automatically place it where 9487 is placed today.

Can someone help me?

Thank you ind advance

Mikael

I am running query in a another system where the numbers of rows can
be 15 as well as 35000. I have tried the "supersize" method but it
does not function when the cells are empty... any ideas???

Hi,

That is correct, It will not works if there are blank cells. Also, as a
best practise, please avoid leaving rows. columns or cells blank. If there
is any such blank, please type DUMMY else you could face problems with
sorting, filtering and pivoting.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

wrote in message
...
I am running query in a another system where the numbers of rows can
be 15 as well as 35000. I have tried the "supersize" method but it
does not function when the cells are empty... any ideas???