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
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 62.3K Get Help
 364 Global Discussions
 199 Industry Talk
 428 Announcements
 4.4K Ideas & Feature Requests
 136 Brandfolder
 127 Just for fun
 128 Community Job Board
 445 Show & Tell
 28 Member Spotlight
 1 SmartStories
 283 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!