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 mix-ups cost me way too many brain cells!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 145 Just for fun
- 62 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!