#Divide by Zero Error

Hello,

It's been awhile since i wrote a big formula. I just need some guidance on getting my formula to return to 0 if there is no data to populate a value. As you can see i have some other IFERROR logic in my formula but for the overall sum I cannot for the life of me figure this one out.


=SUMIFS({1st Reply Duration}, {1st Reply Duration}, ISNUMBER(@cell), {Replied}, ISDATE(@cell), {Replied}, (IFERROR(MONTH(@cell), 0) = 5), {Replied}, (IFERROR(YEAR(@cell), 0) = 2024)) / COUNTIFS({1st Reply Duration}, ISNUMBER(@cell), {Replied}, ISDATE(@cell), {Replied}, (IFERROR(MONTH(@cell), 0) = 5), {Replied}, (IFERROR(YEAR(@cell), 0) = 2024))

Answers

  • bisaacs
    bisaacs ✭✭✭✭✭

    Hey @tim.curtin25,

    Would you be able to provide a screenshot of your sheet that you're using this formula in? Please be sure to obscure any sensitive information

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • @bisaacs Sure thing. The columns at the end are the ones utilized for the formula.

    Date Replied = {Replied}



  • bisaacs
    bisaacs ✭✭✭✭✭

    Thanks @tim.curtin25!

    I'm assuming the duration columns are just calculating the difference between Date Recd, Date Replied, and Date Email Forwarded?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

  • bisaacs
    bisaacs ✭✭✭✭✭

    @tim.curtin25 Hmmmmm. Could you make the formula part of an IF statement that checks if specific cells are blank? Something like:

    =IF(OR(ISBLANK([Your Column]@row), ISBLANK([Other Column]@row)), "0", SUMIFS({1st Reply Duration}, {1st Reply Duration}, ISNUMBER(@cell), {Replied}, ISDATE(@cell), {Replied}, (IFERROR(MONTH(@cell), 0) = 5), {Replied}, (IFERROR(YEAR(@cell), 0) = 2024)) / COUNTIFS({1st Reply Duration}, ISNUMBER(@cell), {Replied}, ISDATE(@cell), {Replied}, (IFERROR(MONTH(@cell), 0) = 5), {Replied}, (IFERROR(YEAR(@cell), 0) = 2024))

    Would that maybe work?

    If my response was helpful in any way (or answered your question) please be sure to upvote it, mark it as awesome, or mark it as the accepted answer!

    I'm always looking to connect with other industry professionals, feel free to connect with me on LinkedIn as well!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!