SumIF based on a value in drop down box

[Justinmih]

Hi there,
I am trying to put a formula in cell c42 based on the Value of Cell C2 which has a drop down box with "Buy" and "sell" however it keeps returning a value of zero.

If you select "Sell" in C2 then the sum should be C4-C10*C3
If you select "Buy" in C2 the the sum should be C10-C4*C3

Please see formula below

=SUM(IF(C2="Sell",C4-C10)*C3)*(IF(C2="buy",C10-C4)*C3)))

If anyone can help me it would be greatly appreciated.

Thanks
Justin.

[jeffreybrown]

Hi Justin,

Maybe something like

=CHOOSE(MATCH(C2,{"Buy","Sell"},0),C10-C4,C4-C10)*C3

[Haseeb Avarakkan]

May be,

=(C4-C10)*C3*IF(C2="Buy",-1,1)

[Justinmih]

Hi Jeffrey,
thanks for the quick reply.
It works when you select "Sell" however when you select "Buy" it comes up with #N/A. Any ideas?

cheers
Justin.

[jeffreybrown]

@ Justin, I seem to have no problems with the formula. Check the spelling and make sure it is all the same.

@ Haseeb, very nice as usual

[Justinmih]

Hi Haseeb,
Than you too for replying.
What you have given me works for the "Buy" how does it work when sell is selected though?

Cheers

Justin.

[Justinmih]

Hi Still no joy, just copied it across from yours.
Would it be something to do with it being an Excel for Mac2011?

[jeffreybrown]

I have no experience with Mac so I don't quite now, but does this sample help?

[Justinmih]

Hi Jeffrey,

Just deleted the Buy and Sell and re-entered them into the drop dow list. It works perfect now.

thanks again its much appreciated.

cheers

Justin.

[jeffreybrown]

You're very welcome and glad to hear you got it all sorted. Thanks for the feedback.