sum only positive numbers for certain groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

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...
 
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.
 
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,
 
many thanks!

Farhad said:
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,
Click to expand...
 
Back
Top