# How to calculate a sum total based on certain criterias

#### jenesis33

##### New Member
Hello, I need help with a formula that can calculate the sum total based on a text in a cell.

-I am addressing the Net Profit column (J).
-Calculate the sum to be positive if Buy/Sell/Win/Lose (column C)) displays a BW or SW.
-Calculate the sum to be a negative if Buy/Sell/Win/Lose displays a BL or SL.

-Here is my working formula, but I can't seem to get it right:
=SUMIF(C6=”BL”,(G6+H6+I6)*-1,IF(C6=”WL”,(G6+H6+I6)*-1,IF(C6=”BW”,(G6+H6+I6),IF(C6=”LW”,G6+H6+I6))))

I was able to get the negative value in J10 & J11 by multiplying the sum by -1. (Ex. =SUM(G11:I11)*-1)

View attachment 21318

Any help will be greatly appreciated. It's been years since I have worked with Excel and I am stumped.

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

#### jenesis33

##### New Member
Table is shown as an attachment. I don't know why it didn't show in the post. #### Thomas26

##### New Member
Hi Jenesis33,

I believe this is the answer that you are looking for:

=IF(OR(C6="BW",C6="SW"),G6+H6+I6,IF(OR(C6="BL",C6="SL"),-(G6+H6+I6),""))

Replies
6
Views
62
Replies
3
Views
52
Replies
1
Views
105
Replies
3
Views
44
Replies
0
Views
321