sum only positive numbers for certain groups
 

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

sum only positive numbers for certain groups
 

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$990),($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 he
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.

SteveC wrote:

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

sum only positive numbers for certain groups
 

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$1000),$B$2:$B$100))

you can extend the range to what ever you want.

Thanks,
--
Farhad Hodjat


"SteveC" wrote:

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

sum only positive numbers for certain groups
 

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

"Dave Peterson" wrote:

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$990),($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 he
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.

SteveC wrote:

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

sum only positive numbers for certain groups
 

many thanks!

"Farhad" wrote:

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$1000),$B$2:$B$100))

you can extend the range to what ever you want.

Thanks,
--
Farhad Hodjat


"SteveC" wrote:

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