# 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

If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

#### Fluff

##### MrExcel MVP, Moderator
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})``

#### chrismid259

##### New Member
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.

#### Fluff

##### MrExcel MVP, Moderator
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
2
Views
111
Replies
0
Views
54
Replies
2
Views
678
Replies
9
Views
543
Replies
2
Views
172

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,151,821
Messages
5,766,641
Members
425,365
Latest member
MrLupin

### 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