How to calculate a sum total based on certain criterias

jenesis33

New Member
Joined
Aug 29, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Some videos you may like

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
Joined
Aug 29, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
Table is shown as an attachment. I don't know why it didn't show in the post.
BinaryExample.jpg
 

Thomas26

New Member
Joined
Aug 29, 2020
Messages
1
Office Version
  1. 2010
Platform
  1. Windows
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),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,108,527
Messages
5,523,391
Members
409,515
Latest member
chemitek

This Week's Hot Topics

Top