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 :)

Find more posts tagged with

Accepted answers

WilliamPaschall

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.

All comments

WilliamPaschall

Hello JBYYC,

=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

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.