'sumproduct" two columns only if positive

[mathipau]

Hello all,

A B
[TABLE="width: 128"]
<colgroup><col width="64" style="width:48pt" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent, align: right"]7[/TD]
[TD="width: 64, bgcolor: transparent, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]8[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent, align: right"]-4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]-3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]-8
[/TD]
[/TR]
</tbody>[/TABLE]

I think I have a similar problem.
How to "sumproduct" these two arrays, but only taking into account positive values of column B ? The result I am looking for is therefore 28+48+24=100.

Thanks a lot,

 

[RoryA]

Re: List only cells with positive values

Welcome to the forum.

Try:
=SUMPRODUCT(A1:A6,B1:B6*(B1:B6>0))

 

[mathipau]

Hello everyone,

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]7
[/TD]
[TD]4
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]9
[/TD]
[TD]-4
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]-3
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]2
[/TD]
[TD]-8
[/TD]
[/TR]
</tbody>[/TABLE]

I would like to apply the sumproduct formula to this table, but I need the formula to return results only for positive values in column B. So in this case, I need a formula that ignores rows 3, 5 and 6 because their values in column B are negative. I therefore need 100 as the end result ((7*4)+(8*6)+(4*6).

Thanks a lot for your help!

 

[mathipau]

Re: List only cells with positive values

Perfect

Thanks Rory!

 

[AlanY]

try

=SUMPRODUCT(A1:A6,(B1:B6>0)*B1:B6)

 

[RoryA]

Re: List only cells with positive values

Glad to help.

Note: I merged these posts into your other thread. In future, please only post the question in one place. Thanks.

 

[oldbrewer]

[TABLE="width: 640"]
<colgroup><col width="64" span="10" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]col a[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]28[/TD]
[TD][/TD]
[TD]row 3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]48[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD="align: right"]-4[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]24[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD="align: right"]-3[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]-8[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD]row 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"]100[/TD]
[TD="colspan: 2"]answer from macro[/TD]
[TD][/TD]
[TD]row 10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 7"]I believe sumproduct will only return values of your numerical data column[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]when any other conditions are satisfied[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]multiplying cols A and B together[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]when Col B >0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]seems easier[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 3"]otherwise a very simple macro[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]for j=3 to 8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 5"]if cells(j,2)>0 then sum=sum+(Cells(j,2)*cells(j,1))[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]next j[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]cells(10,3)=sum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]end sub[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]