# SUMPRODUCT - IF statement in array

#### fun2excel4money

##### New Member
Hello Community,

I've been stumped with a question for a bit while using a SUMPRODUCT. Below I have a fairly simple of example of what I want to accomplish. If the stock price (\$20) is greater than the strike price, I want to take the difference and multiply it by the units. If I have a stock price (\$20) that is less than the strike price, I want to multiply the units by zero. Below, I have a total of 9,500,000. I am attempting to use a SUMPRODUCT like so: =SUMPRODUCT((\$B\$4:\$B\$10)*(C\$2-\$A\$4:\$A\$10)). This formula gives me a result of 9,383,000 as it is still taking the difference between the stock price and the strike price and multiplying it by units. So for the 1,000 unit lot, it is taking (20 - 22) x 1,000 which nets me a negative 2,000 when I want zero. Is there someway to modify this formula such that the SUMPRODUCT will return the correct answer? Many thanks for your help!

 Stock Price 20.00 Strike Price Units 22.00 1,000.00 - 1.00 100,000.00 1,900,000.00 1.00 100,000.00 1,900,000.00 1.00 100,000.00 1,900,000.00 21.15 100,000.00 - 1.00 100,000.00 1,900,000.00 1.00 100,000.00 1,900,000.00 601,000.00 9,500,000.00

<colgroup><col span="3"></colgroup><tbody>
</tbody>

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Dave Patton

##### Well-known Member

Excel 2010
ABCDEF
1Stock Price
220
3Strike PriceUnits
4221,000.0020002000
51100,000.001,900,000.00-1900000
61100,000.001,900,000.00-1900000
71100,000.001,900,000.00-1900000
821.15100,000.00115000115000
91100,000.001,900,000.00-1900000
101100,000.001,900,000.00-1900000
11601,000.009,500,000.00117,000.00
12
13117000
14117000
15117000
2d
Cell Formulas
RangeFormula
F4=(A4-C2)*B4
F8=(A8-C2)*B8
F11=SUM(F4:F10)
B11=SUM(B4:B10)
B13=SUMPRODUCT(--(A4:A10>C2),(A4:A10-C2),B4:B10)
B15=SUMPRODUCT(--(A4:A10>C2),D4:D10)
B14{=SUM(IF(A4:A10>C2,D4:D10))}
C11=SUM(C4:C10)
Press CTRL+SHIFT+ENTER to enter array formulas.

Last edited:

#### fun2excel4money

##### New Member
The result should be 9,500,000. I think you inverted my intended results. I hope I did not communicate it incorrectly. I am also trying to attempt CSE if possible.

#### fun2excel4money

##### New Member
Apologies, I definitely meant AVOID CSE ​if possible

#### fun2excel4money

##### New Member
Dave, B13 got me to the right direction! Just needed it to be this: =SUMPRODUCT(--(A4:A10<C2),(C2-A4:A10),B4:B10). Thanks so much for your help!

#### Dave Patton

##### Well-known Member
Cell B13 uses SumProduct.

#### jtakw

##### Well-known Member
Hi,

Maybe this:

Book1
ABC
1Stock Price
220
3Strike PriceUnits
4221,000.00-
51100,000.001,900,000.00
61100,000.001,900,000.00
71100,000.001,900,000.00
821.15100,000.00-
91100,000.001,900,000.00
101100,000.001,900,000.00
11
12601,000.009,500,000.00
Sheet451
Cell Formulas
RangeFormula
C12=SUMPRODUCT((A4:A10)*(C2-A4:A10)*B4:B10)

#### fun2excel4money

##### New Member
Thank you all! C12 was the formula I wanted without the "--". Dave, apologies as my formula was cut off. The formula should be =SUMPRODUCT(--(A4:A10<C2),(C2-A4:A10),B4:B10); it is your formula but inverted.

#### Dave Patton

##### Well-known Member

Excel 2010
E
149500000
15
2d
Cell Formulas
RangeFormula
E14=SUMPRODUCT(--(A4:A10),C2-A4:A10,B4:B10)

Last edited:

Replies
2
Views
282
Replies
1
Views
369
Replies
4
Views
1K
Replies
3
Views
2K
Replies
6
Views
942

1,171,992
Messages
5,878,646
Members
433,357
Latest member
jack3427dsg546

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back