Sumproduct automatically count number of rows

[ms3348]

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

 

[Gary''s Student]

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


etc.

 

[Ashish Mathur]

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

 

[ms3348]

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

 

[Ashish Mathur]

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