SUMPRODUCT - IF statement in array

[fun2excel4money]

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>

 

[Dave Patton]

What is the correct answer?

Excel 2010
	A	B	C	D	E	F
1			Stock Price
2			20
3	Strike Price	Units
4	22	1,000.00		2000		2000
5	1	100,000.00	1,900,000.00	-1900000
6	1	100,000.00	1,900,000.00	-1900000
7	1	100,000.00	1,900,000.00	-1900000
8	21.15	100,000.00		115000		115000
9	1	100,000.00	1,900,000.00	-1900000
10	1	100,000.00	1,900,000.00	-1900000
11		601,000.00	9,500,000.00			117,000.00
12
13		117000
14		117000
15		117000
2d

Cell Formulas
Range	Formula
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.

 

[fun2excel4money]

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]

Apologies, I definitely meant AVOID CSE ​if possible

 

[fun2excel4money]

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]

Cell B13 uses SumProduct.

Please explain how you calculate the answer that you require.

 

[jtakw]

Hi,

Maybe this:

Book1
	A	B	C
1	Stock Price
2			20
3	Strike Price	Units
4	22	1,000.00	-
5	1	100,000.00	1,900,000.00
6	1	100,000.00	1,900,000.00
7	1	100,000.00	1,900,000.00
8	21.15	100,000.00	-
9	1	100,000.00	1,900,000.00
10	1	100,000.00	1,900,000.00
11
12		601,000.00	9,500,000.00
Sheet451

Cell Formulas
Range	Formula
C12	=SUMPRODUCT((A4:A10)*(C2-A4:A10)*B4:B10)

 

[fun2excel4money]

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]

Excel 2010
	E
14	9500000
15
2d

Cell Formulas
Range	Formula
E14	=SUMPRODUCT(--(A4:A10),C2-A4:A10,B4:B10)