Turn SUMIFS negative value into 0 (ZERO)

  • What do I need to add to this formula to give me zero values for all that are actually pulling negative values?


    I tried adding in less than zero and max at the end. Probably not putting it in the correct place....


    =SUMIFS('GL Detail YTD'!Q:Q,'GL Detail YTD'!P:P,$D20,'GL Detail YTD'!V:V,$J$7)

  • Hello,


    Not quite sure to understand your question ...


    A simple If() function should do the job ... no ...?


    Code
    1. =If(yourformula < 0 , 0, yourformula)

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)

  • Hello,


    You should test following formula :


    Code
    1. =If(Sumifs('GL Detail YTD'!Q:Q,'GL Detail YTD'!P:P,$D20,'GL Detail YTD'!V:V,$J$7)<0,0,Sumifs('GL Detail YTD'!Q:Q,'GL Detail YTD'!P:P,$D20,'GL Detail YTD'!V:V,$J$7))


    Just copy the formula from above ... and paste it into your sheet ..:wink:


    Hope this will help

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)


  • Ahhhh! I see what you were saying. Yes that's it! Thank you!!!!

  • Glad you could fix your problem ... :wink:


    Thanks for your Thanks ...AND for the Like ...:smile:


    P.S.


    Sure you have noticed that NBVC's solution is a lot more elegant ...:wink:

    If you feel like saying "Thank You" for the help received, do not hesitate to click the "Like" icon ...just below...in the bottom right corner...:)