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 "Thumbs Up" icon, below, in the bottom right corner:)

  • That still gives me an error:


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

  • 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 "Thumbs Up" icon, below, in the bottom right corner:)

  • or


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

    Where there is a will there are many ways. Finding one that works for you is the challenge!


    MS Excel MVP 2010-2016


  • 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 "Thumbs Up" icon, below, in the bottom right corner:)