How to optimize IF(return value<0, if true show return value, if false show 0) ?

kancolle

New Member
Joined
Oct 14, 2021
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
I trying to have a cell with formula to show its returned value if <0, but show 0 if >=0. Can we do that in Excel?

Column B is list of different product names in mixed order.
Column C is profit for each row in B where C could be positive or negative numbers.
On the side, I'm trying to summarize, eg. cell E1 to show if a selected product is making loss or not.

First, I pieced together this to find out net profit for a selected product, eg. banana.

cell E1 =SUMIFS(C1:C100,B1:B100,"banana")

Then, I try to combine with IF to have cell E1 reference itself and display value if formula's returned value <0, and display 0 if returned value >=0.

I want to say concisely cell E1 =IF(SUMIFS(C1:C100,B1:B100,"banana")<0,show return value,0)

But Excel is making me re-write that whole segment =IF(SUMIFS(C1:C100,B1:B100,"banana")<0,SUMIFS(C1:C100,B1:B100,"banana"),0)

Is there anyway to optimize it? Thank you.

ps.

By the way, something off-topic, where I used SUMIFS, even though only one condition, is there any benefit to use the older SUMIF? I thought SUMIFS's string format looks more speech intuitive to me and allows for future additional conditions. SUMIFS can have one or more conditions, so why is SUMIF still around and not removed?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
In E1 try: =MIN(SUMIFS(C1:C100,B1:B100,"banana"),0)
 
Upvote 0
Solution
Thank you. It works, but I'm confused why it works.

I was initially trying to write "If formula returns <0, then show that negative returned value, but if >=0 then show 0). Was expecting Excel to be able to support this logic and intuitiveness.

I found articles about using MIN to find the minimum value in a group of numbers, but it doesn't talk about the method how you've applied it. Would you be able to explain more or point me to a tutorial page?

When I read your formula, because SUMIFS should return only one value, so I can understand MIN would be grabbing the only one value it finds here.

MIN(number1, number2, number3.....) and therefore MIN(number1) still works since there is only one choice.

I haven't found article explaining how ,0) at the end modifies the formula statement. Then, I tried test replacing MIN with MAX thinking they should work the same, but E1 shows 0 when find value <0. Even though E1 is <0, shouldn't MAX treat the only value it can find as the largest number?
 
Upvote 0
Thank you. It works, but I'm confused why it works.

I was initially trying to write "If formula returns <0, then show that negative returned value, but if >=0 then show 0). Was expecting Excel to be able to support this logic and intuitiveness.

I found articles about using MIN to find the minimum value in a group of numbers, but it doesn't talk about the method how you've applied it. Would you be able to explain more or point me to a tutorial page?

When I read your formula, because SUMIFS should return only one value, so I can understand MIN would be grabbing the only one value it finds here.

MIN(number1, number2, number3.....) and therefore MIN(number1) still works since there is only one choice.

I haven't found article explaining how ,0) at the end modifies the formula statement. Then, I tried test replacing MIN with MAX thinking they should work the same, but E1 shows 0 when find value <0. Even though E1 is <0, shouldn't MAX treat the only value it can find as the largest number?
Think about it this way: if sumifs value is less than 0 you want to show it. If greater than or equal to 0 you want the formula to return 0. Any negative value will ALWAYS be less than 0. So, the formula which looks for the minimum value among the two values will always return the sumifs result if it is less than 0. It will always return 0 if the sumifs is greater than 0.
 
Upvote 0
Thank you for your explanation. My head was not turning to look at Sumifs value as just X.

X=Sumifs value, so Min(X,0)

Now I see :)
 
Upvote 0
Thank you for your explanation. My head was not turning to look at Sumifs value as just X.

X=Sumifs value, so Min(X,0)

Now I see :)
You are welcome - thanks for the reply.
 
Upvote 0
Would this same concept work for the opposite? I'd like my cell to return 0 if the sum of 2 cells is less than 0 and return the value of the 2 cells is it's 0 or more.
 
Upvote 0
Would this same concept work for the opposite? I'd like my cell to return 0 if the sum of 2 cells is less than 0 and return the value of the 2 cells is it's 0 or more.
You don't need the MIN function for that. Let's say your 2 cells are A1 & B1, then in C1 enter:
Excel Formula:
=IF(SUM(A1:B1)<0,0,SUM(A1:B1))
An alternative would be:
Excel Formula:
=MAX(SUM(A1:B1),0)
 
Upvote 0
You don't need the MIN function for that. Let's say your 2 cells are A1 & B1, then in C1 enter:
Excel Formula:
=IF(SUM(A1:B1)<0,0,SUM(A1:B1))
An alternative would be:
Excel Formula:
=MAX(SUM(A1:B1),0)
Thanks Joe, I'm getting the 0 when the sum is less than 0 but I'm getting a 0 when the sum is greater than 0 as well. G4= (f4-b4) and the result is -1.94, need it to say 0. Then G7= (f7-b7) and the result is 69.75, need it to say 69.75.
 
Upvote 0
Thanks Joe, I'm getting the 0 when the sum is less than 0 but I'm getting a 0 when the sum is greater than 0 as well. G4= (f4-b4) and the result is -1.94, need it to say 0. Then G7= (f7-b7) and the result is 69.75, need it to say 69.75.
You don't need the MIN function for that. Let's say your 2 cells are A1 & B1, then in C1 enter:
Excel Formula:
=IF(SUM(A1:B1)<0,0,SUM(A1:B1))
An alternative would be:
Excel Formula:
=MAX(SUM(A1:B1),0)
My mistake, it is working. Thank you a million times.
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top