Sumproduct automatically count number of rows

M

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
 
A

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
 
M

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

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top