Help with SUBTOTAL formula

  • Thread starter Thread starter PO
  • Start date Start date
P

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