Buy & sell formula

[Sarah7828]

Hi

I have the following data:

ColumnA. Column B
Buy 3
Sell -2
Buy 4
Sell -6

I have created a Sumif function as follows:


Sumif(a:a, “Buy”, b:b) - sumif(a:a, “Sell”, b:b)


However , it doesn’t provide me with the correct net amount as the sells are negative values

Is there away I can you the ABS function in the sell formula to get me the result? Or is there a better formula altogether?

Thanks

 

[myall_blues]

There's several ways. Because your sell amounts are negative, then you should add the SUMIFS

=SUMIF(A1:A4,"Buy",B1:B4)+SUMIF(A1:A4,"Sell",B1:B4)

or you could take the absolute value of the sells and subtract

=SUMIF(A1:A4,"Buy",B1:B4)-ABS(SUMIF(A1:A4,"Sell",B1:B4))

or, again because the sell amounts are negative, you could just sum column B

=SUM(B1:B4)

All of these give the same answer (-1)

 

[Sarah7828]

There's several ways. Because your sell amounts are negative, then you should add the SUMIFS

=SUMIF(A1:A4,"Buy",B1:B4)+SUMIF(A1:A4,"Sell",B1:B4)

or you could take the absolute value of the sells and subtract

=SUMIF(A1:A4,"Buy",B1:B4)-ABS(SUMIF(A1:A4,"Sell",B1:B4))

or, again because the sell amounts are negative, you could just sum column B

=SUM(B1:B4)

All of these give the same answer (-1)

Thank you so much . . I will use the 2nd .- take you for confirming