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

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

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

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