Adding and subtracting depending on selection in column

[chrismid259]

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.

 

[Fluff]

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

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

 

[chrismid259]

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

=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]

With 2019 you may need to confirm the formula with Ctrl Shift Enter rather than just Enter

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H
1
2		a	buy	1	10			-5
3		b			1
4		c			1
5		a	sell		20
6		b			1
7		c			1
8		a	buy		5
9		b			1
10		c			1
11
Main

Cell Formulas
Range	Formula
H2	H2	=SUM(SUMIFS(E2:E100,B2:B100,"a",C2:C100,{"buy","Sell"})*{1,-1})
Press CTRL+SHIFT+ENTER to enter array formulas.