Excluding Hidden Rows from calculations


T

Terry Bennett

Not sure if this can be done??

I have a worksheet where a number of the rows are hidden and need to exclude
these from calculations performed using SUMPRODUCT, ie;

=SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1))

but only looking at rows that are not hidden.

Is there a relatively simple way of doing this? If not I will just create
another column to show an 'X' when the row is not to be counted and then
make the calculation:

=SUMPRODUCT(--(A1:A2000=C1)*(B1:B2000=D1)*(E1:E2000<>"X"))

Thanks.
 
Ad

Advertisements

P

Peo Sjoblom

It depends on the Excel version, 2003 and later can ignore hidden as in
format>row>hide,
earlier versions can ignore hidden by a filter
Also if you are going to use unary minuses it should look like this\


=SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1))

otherwise you might as well remove it and use


=SUMPRODUCT((A1:A2000=C1)*(B1:B2000=D1))



Anyway with 2003 and later and hidden rows as opposed to filtered rows us

=SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1),SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A$2000)-MIN(ROW($A$1:$A$1000)),,)))



--


Regards,


Peo Sjoblom
 
T

Terry Bennett

Wow!

Many thanks Peo.


Peo Sjoblom said:
It depends on the Excel version, 2003 and later can ignore hidden as in
format>row>hide,
earlier versions can ignore hidden by a filter
Also if you are going to use unary minuses it should look like this\


=SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1))

otherwise you might as well remove it and use


=SUMPRODUCT((A1:A2000=C1)*(B1:B2000=D1))



Anyway with 2003 and later and hidden rows as opposed to filtered rows us

=SUMPRODUCT(--(A1:A2000=C1),--(B1:B2000=D1),SUBTOTAL(103,OFFSET($A$1,ROW($A$1:$A$2000)-MIN(ROW($A$1:$A$1000)),,)))



--


Regards,


Peo Sjoblom
 
Ad

Advertisements


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