Help with SUBTOTAL formula

[PO]

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

 

[Roger Govier]

Hi

Without using the Autofilter, you could use
=SUMPRODUCT(--($B$1:$B$100>0),--($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$100>0),--($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.