Ratio of growth over a filtered set
 

Assume the following sheet, with columns titles Color, Yesterday, Today

Blue 5 7
Blue 0 9
Red 3 9

It is filtered using an autofilter on Color, and I need to show the total
for Today, but I need to exclude rows with a 0 value in the Yesterday column.

For instance, when filtered on Blue, I need to get 7

I realize I can use SUBTOTAL to get the sum of the C column across the
filtered rows, how do I exclude rows with a 0 value in column B from the
SUBTOTAL ?

Ratio of growth over a filtered set
 

Assuming that A2:C4 contains the data, try...

=SUMPRODUCT(--($B$2:$B4<0),SUBTOTAL(9,OFFSET($C$2:$C$4,ROW($C$2 :$C$4)-RO
W($C$2),0,1)))

Hope this helps!

In article ,
AlanKohl wrote:

Assume the following sheet, with columns titles Color, Yesterday, Today

Blue 5 7
Blue 0 9
Red 3 9

It is filtered using an autofilter on Color, and I need to show the total
for Today, but I need to exclude rows with a 0 value in the Yesterday column.

For instance, when filtered on Blue, I need to get 7

I realize I can use SUBTOTAL to get the sum of the C column across the
filtered rows, how do I exclude rows with a 0 value in column B from the
SUBTOTAL ?

Ratio of growth over a filtered set
 

Yes, it works. I wish I would understand it though. Where can I find a
description of what "--" does ?

Many thanks.


"Domenic" wrote:

Assuming that A2:C4 contains the data, try...

=SUMPRODUCT(--($B$2:$B4<0),SUBTOTAL(9,OFFSET($C$2:$C$4,ROW($C$2 :$C$4)-RO
W($C$2),0,1)))

Hope this helps!

In article ,
AlanKohl wrote:

Assume the following sheet, with columns titles Color, Yesterday, Today

Blue 5 7
Blue 0 9
Red 3 9

It is filtered using an autofilter on Color, and I need to show the total
for Today, but I need to exclude rows with a 0 value in the Yesterday column.

For instance, when filtered on Blue, I need to get 7

I realize I can use SUBTOTAL to get the sum of the C column across the
filtered rows, how do I exclude rows with a 0 value in column B from the
SUBTOTAL ?

Ratio of growth over a filtered set
 

In article ,
AlanKohl wrote:

Yes, it works. I wish I would understand it though.

Have a look here...

http://j-walk.com/ss/excel/eee/eee001.txt

Where can I find a
description of what "--" does ?

Have a look here...

http://www.mcgimpsey.com/excel/formulae/doubleneg.html

Many thanks.

You're very welcome!