sum only positive numbers for certain groups

[Guest]

Col A Col B Col C
apples -1
apples 3
apples 1 4
oranges -1
oranges -5
bananas 1 1

What formula in C will sum only positive numbers for each group of lables,
as indicated above, leaving other cells blank?

haven't figured out how to adjust sumproduct yet... thanks...

 

[Dave Peterson]

Put this in C1 and drag down

=IF(COUNTIF($A$1:A1,A1)<COUNTIF($A$1:$A$99,A1),"",
SUMPRODUCT(--($A$1:$A$99=A1),--($B$1:$B$99>0),($B$1:$B$99)))

Adjust the ranges to match--but you can't use whole columns (except in xl2007).

=sumproduct() likes to work with numbers. The -- stuff changes trues and falses
to 1's and 0's.

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html

===
This will return 0 for the Oranges, though.

 

[Guest]

Hi,

First sort your data based on column A then enter this formula in C2 and
then copy drag down to where ever you need:

=IF(A2=A1,"",SUMPRODUCT(--($A$2:$A$100=A1),--($B$2:$B$100>0),$B$2:$B$100))

you can extend the range to what ever you want.

Thanks,

 

[Guest]

thanks very much Dave for the formula and the links...

 

[Guest]

many thanks!

Hi,

First sort your data based on column A then enter this formula in C2 and
then copy drag down to where ever you need:

=IF(A2=A1,"",SUMPRODUCT(--($A$2:$A$100=A1),--($B$2:$B$100>0),$B$2:$B$100))

you can extend the range to what ever you want.

Thanks,