Adding and subtracting depending on selection in column

chrismid259

New Member
Joined
May 2, 2010
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,590
Office Version
  1. 365
Platform
  1. Windows
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
Joined
May 2, 2010
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
65,590
Office Version
  1. 365
Platform
  1. Windows
With 2019 you may need to confirm the formula with Ctrl Shift Enter rather than just Enter
+Fluff 1.xlsm
ABCDEFGH
1
2abuy110-5
3b1
4c1
5asell20
6b1
7c1
8abuy5
9b1
10c1
11
Main
Cell Formulas
RangeFormula
H2H2=SUM(SUMIFS(E2:E100,B2:B100,"a",C2:C100,{"buy","Sell"})*{1,-1})
Press CTRL+SHIFT+ENTER to enter array formulas.
 

Forum statistics

Threads
1,147,497
Messages
5,741,498
Members
423,662
Latest member
Ajmal Khursand

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