CODEWITHSUNDEEP
google-sheets
John
John

Reputation: 1297

Use text in a cell as plus and minus

I have a sheet with multiple rows. There is a cell on each row that says "In" and "Out" in a dropdown. Basically this is plus and minus. Next to that I have a cell that that holds an amount.

So it looks like this:

Item 1, In, 10
Item 2, Out, 5

This totals a profit of 5.

How can I with this setup calculate the total profit/loss for all the cells in the sheet depending on multiple in/out rows?

Upvotes: 0

Views: 916

Answers (1)

Brad Smith
Brad Smith

Reputation: 185

An if formula will calculate the rows and a sum formula will calculate the column.

First make the numbers positive and negative values.

=IF(B2="In",C2,C2*-1)

This formula in 'D2' checks if the value of 'B2' is "In".

  • If yes then just adds the value of 'C2'.
  • If no then it multiplies the value of 'C2' by negative one.

Second step is to add up the numbers.

=SUM(D2:D3)

Cell 'D4' sums the column.

enter image description here

Upvotes: 1

Related Questions