Adding and subtracting depending on selection in column

chrismid259

New Member
Hello,

I'm creating a spreadsheet to track my stocks and shares profit and losses. I'm trying to create a formula that will add or subject based on whether I've selected buy (which would add) or sell (which would minus) the amount in the total holding of that certain stock. So, as an example. I have 200 shares whatever stock, say TSLA for this example. I have a formula that adds the total holdings in pounds depending on if the ticker symbol column has TSLA in a cell then adds those up.

But I need a formula that would be able to subtract from the total holdings if 'sell' is selected. Right now I have; =SUMIF(B2:B3,"TSLA",E2:E1000)

Column B is the ticker symbol

Column C is where I select whether the stock has been bought 'buy' or sold 'sell'.

Column E is where the total amount of holding in pounds is displayed.

I hope this makes sense and that someone can help out. Thanks.

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
``=SUM(SUMIFS(E2:E100,B2:B100,"a",C2:C100,{"buy","Sell"})*{1,-1})``

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe
Excel Formula:
``=SUM(SUMIFS(E2:E100,B2:B100,"a",C2:C100,{"buy","Sell"})*{1,-1})``
Thanks for replying. I'm using Microsoft Excel 2019 and have updated my profile now. I've given this formula a go but seems to just be adding all the figures up.

With 2019 you may need to confirm the formula with Ctrl Shift Enter rather than just Enter
+Fluff 1.xlsm
ABCDEFGH
1
3b1
4c1
5asell20
6b1
7c1
9b1
10c1
11
Main
Cell Formulas
RangeFormula
Press CTRL+SHIFT+ENTER to enter array formulas.

Replies
1
Views
303
Replies
4
Views
376
Replies
3
Views
809
Replies
0
Views
127
Replies
2
Views
5K

1,196,503
Messages
6,015,577
Members
441,902
Latest member
alhaste

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.

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