How to do Sumproduct function with only values greater than zero

[alex13392]

Hi, so my problem is fairly simple im just struggling to get the right result.

My data is effectively something like this

A	B
5	8
2	10
0	9
0	2
11	6

<tbody>
</tbody>

What i want to do is do a sumproduct of the two columns for only the rows when A is greater than zero, (i.e. only rows 2,3 and 6 in the example)

The formula i have found to do this on other forums is something like:

=sumproduct(--(A2:A6=">0"),A2:A6,B2:B6))

But this just comes up with zero.

If anyone has any ideas i would mega appreciate it!

Thanks

 

[daverm]

Hi, so my problem is fairly simple im just struggling to get the right result.

My data is effectively something like this

A	B
5	8
2	10
0	9
0	2
11	6

<tbody>
</tbody>

What i want to do is do a sumproduct of the two columns for only the rows when A is greater than zero, (i.e. only rows 2,3 and 6 in the example)

The formula i have found to do this on other forums is something like:

=sumproduct(--(A2:A6=">0"),A2:A6,B2:B6))

But this just comes up with zero.

If anyone has any ideas i would mega appreciate it!

Thanks

The way sumproduct works is that sumproduct(A2:A6,B2:B6) = A2*B2 + A3*B3 + A4*B4 + A5*B5 + A6*B6

So if a Value in column A is 0, then that part of the sum will be 0.

i.e. in your example sumproduct({5,2,0,0,11},{8,10,9,2,6}) = 5*8 + 2*10 + 0*9 + 0*2 + 11*6 = 40 + 20 + 0 + 0 +66 = 126

so the formula would just be =sumproduct(A2:A6,B2:B6)

 

[liveinhope]

=SUM((IF($A2:$A6>0,$A2:$A6,0)*B2:B6)) Enter this formula in a single cell. Then instead of press enter. do CTRLSHIFT-ENTER

that turns the formula into an array formula

This also works when a value in "A" is less than zero (sorry Dave your formula will multiple the negsative number in A by the matcing number in B)

 

[daverm]

=SUM((IF($A2:$A6>0,$A2:$A6,0)*B2:B6)) Enter this formula in a single cell. Then instead of press enter. do CTRLSHIFT-ENTER

that turns the formula into an array formula

Aha, I see I have missed the point with regards to negatives in A. Apologies. I didn't think through the implications of the question properly given the example numbers.

 

[Aladin Akyurek]

Try the following correction...

=SUMPRODUCT(--(A2:A6>0),A2:A6,B2:B6)

This allows you to avoid negative numbers in the A-range, otherwise the test term is not needed:

=SUMPRODUCT(A2:A6,B2:B6)