Hello,

I want to sum the values in column A if the corresponding values in column B
are greater then or equal to 0.
In the example below the formula should return 120:

ColA ColB ColC
100 -3 Cats
50 1 Dogs
70 2 Cats

The problem is that the table uses an autofilter and, if the table is
filtered, the formula should only sum the visible rows (still using the
condition mentioned above). If, for instance, the table is filtered on
"Cats" the formula should return 70.

The only formula I know that reacts to autofilter is the SUBTOTAL formula
but, as far as I know, I can't use conditions in the SUBTOTAL formula.

Any ideas?

Regards
PO

Hi

Without using the Autofilter, you could use
=SUMPRODUCT(--($B$1:$B$1000),--($C$1:$C$100="Cats"),$A$1:$A$100)

If you put Cats or Dogs in cell D1, then change formula to
=SUMPRODUCT(--($B$1:$B$1000),--($C$1:$C$100=D1),$A$1:$A$100)

then just change the value in D1 as appropriate.
Change size of ranges to suit, but do ensure that they are all of equal
length, and note that Sumproduct cannot take whole columns B:B as its
arguments.

--
Regards

Roger Govier


"PO" <h wrote in message ...
Hello,

I want to sum the values in column A if the corresponding values in
column B are greater then or equal to 0.
In the example below the formula should return 120:

ColA ColB ColC
100 -3 Cats
50 1 Dogs
70 2 Cats

The problem is that the table uses an autofilter and, if the table is
filtered, the formula should only sum the visible rows (still using
the condition mentioned above). If, for instance, the table is
filtered on "Cats" the formula should return 70.

The only formula I know that reacts to autofilter is the SUBTOTAL
formula but, as far as I know, I can't use conditions in the SUBTOTAL
formula.

Any ideas?

Regards
PO