SUMPRODUCT - IF statement in array

fun2excel4money

New Member
Joined
Dec 24, 2016
Messages
18
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 PriceUnits
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
Joined
Feb 15, 2002
Messages
5,277
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
What is the correct answer?


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
Joined
Dec 24, 2016
Messages
18
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
Joined
Dec 24, 2016
Messages
18
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
Joined
Feb 15, 2002
Messages
5,277
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Cell B13 uses SumProduct.

Please explain how you calculate the answer that you require.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Dec 24, 2016
Messages
18
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
Joined
Feb 15, 2002
Messages
5,277
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

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

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top