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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,757
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In E1 try: =MIN(SUMIFS(C1:C100,B1:B100,"banana"),0)
 
Solution

kancolle

New Member
Joined
Oct 14, 2021
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
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?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,757
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

kancolle

New Member
Joined
Oct 14, 2021
Messages
6
Office Version
  1. 2007
Platform
  1. Windows
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 :)
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,757
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,111
Messages
5,768,159
Members
425,458
Latest member
Jaspal1996

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
Top