Help with sum formula counting blank cells as zero
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

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

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.

@WilliamPaschall ... THANK YOU! I'm still a novice and these little mixups cost me way too many brain cells!
Help Article Resources
Categories
Check out the Formula Handbook template!