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?
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?