Help with sum formula counting blank cells as zero

Options

Hiya again Smarthsheet community! Hoping someone much smarter than me can help with this formula issue.

I have a formula that sums six cells that I want to sum and divide by 30. This then generates a related IF formula to return a text value in another cell (High, Medium, Low, None). All is generally working fine, but it's counting blank cells as 0, and thus the answer is 0, and retuning None, when it should be left blank.

I've fiddled like crazy with both the sum formula (ISNUMBER, SUMSIF etc) and the dependent formula. but just can't seem to make it work.

Working formula: =VALUE(SUM(drhelper@row, orghelper@row, sphelper@row, caphelper@row, bhelper@row, mhelper@row) / 30)

Dependent formula: =IF(impacthelper@row = "", "", IF(impacthelper@row >= 0.53, "High", IF(impacthelper@row >= 0.4, "Medium", IF(impacthelper@row >= 0.03, "Low", IF(impacthelper@row = 0, "None")))))

SO APPRECIATE ANY HELP :)

Best Answer

  • WilliamPaschall
    WilliamPaschall ✭✭✭✭
    Answer ✓
    Options

    Hello JBYYC,

    I think I have something that will help. If I am understanding your question correctly, you are wanting the cell that has the IF formula in it to be blank if the helper cells are blank. If this is not the case, please let me know. If that is the case, then try putting

    =IF(AND(ISBLANK(drhelper@row), ISBLANK(orghelper@row), ISBLANK(sphelper@row), ISBLANK(caphelper@row), ISBLANK(bhelper@row), ISBLANK(mhelper@row)), "", (continue with your dependent formula)

    See if this helps you out.

Answers

  • WilliamPaschall
    WilliamPaschall ✭✭✭✭
    Answer ✓
    Options

    Hello JBYYC,

    I think I have something that will help. If I am understanding your question correctly, you are wanting the cell that has the IF formula in it to be blank if the helper cells are blank. If this is not the case, please let me know. If that is the case, then try putting

    =IF(AND(ISBLANK(drhelper@row), ISBLANK(orghelper@row), ISBLANK(sphelper@row), ISBLANK(caphelper@row), ISBLANK(bhelper@row), ISBLANK(mhelper@row)), "", (continue with your dependent formula)

    See if this helps you out.

  • JBYYC
    JBYYC ✭✭✭
    Options

    @WilliamPaschall ... THANK YOU! I'm still a novice and these little mix-ups cost me way too many brain cells!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!