# 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

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### 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
80
Replies
0
Views
178
Replies
1
Views
296
Replies
9
Views
100
Replies
3
Views
163

1,129,587
Messages
5,637,227
Members
416,963
Latest member
samfuge

### 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.

### Which adblocker are you using?

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

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