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