Excluding Hidden Rows from calculations
 

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.

Excluding Hidden Rows from calculations
 

It depends on the Excel version, 2003 and later can ignore hidden as in
formatrowhide,
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




"Terry Bennett" wrote in message
...
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.

Excluding Hidden Rows from calculations
 

Wow!

Many thanks Peo.


"Peo Sjoblom" wrote in message
...
It depends on the Excel version, 2003 and later can ignore hidden as in
formatrowhide,
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




"Terry Bennett" wrote in message
...
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.

Excluding Hidden Rows from calculations
 

You are welcome


--

Regards,

Peo Sjoblom





"Terry Bennett" wrote in message
...
Wow!

Many thanks Peo.


"Peo Sjoblom" wrote in message
...
It depends on the Excel version, 2003 and later can ignore hidden as in
formatrowhide,
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




"Terry Bennett" wrote in message
...
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.