Nested IF formula

I am attempting to count cells that have text or not and using this formula:

=IF(SUM(IF(SUM((IF(ISBLANK([T2 Last Name]@row), 0, 1) + IF(ISBLANK([T3 Last Name]@row), 0, 1)) + IF(ISBLANK([T4 Last Name]@row), 0, 1))) + (IF(ISTEXT([Last Name]@row), 1, 0))), 0, "")

If the cell has text I want it to count 1 - if not then 0 and then sum them at the end. This works great but then puts a 0 in the cell. I can't hide the 0 so tried to add another IF/Then to make the cell blank if ALL the cells are blank - or the row is blank - and I'm getting an INCORRECT error. Is there a less complex formula? Can you see my error?

Thanks - new to the community!

Find more posts tagged with

Formulas

functionality

Accepted answers

Paul Newcome

This will give you your count.

=IF([T2 Last Name]@row <> "", 1) + IF([T3 Last Name]@row <> "", 1) + IF([T4 Last Name]@row <> "", 1) + IF([Last Name]@row <> "", 1)

To "hide" a zero, you take your original formula and drop it into an IF statement that basically says that IF it is GREATER THAN ZERO, the run ORIGINAL FORMULA.

=IF(original formula > 0, original formula)

=IF(IF([T2 Last Name]@row <> "", 1) + IF([T3 Last Name]@row <> "", 1) + IF([T4 Last Name]@row <> "", 1) + IF([Last Name]@row <> "", 1) > 0, IF([T2 Last Name]@row <> "", 1) + IF([T3 Last Name]@row <> "", 1) + IF([T4 Last Name]@row <> "", 1) + IF([Last Name]@row <> "", 1))

All comments

Paul Newcome

This will give you your count.

=IF([T2 Last Name]@row <> "", 1) + IF([T3 Last Name]@row <> "", 1) + IF([T4 Last Name]@row <> "", 1) + IF([Last Name]@row <> "", 1)

To "hide" a zero, you take your original formula and drop it into an IF statement that basically says that IF it is GREATER THAN ZERO, the run ORIGINAL FORMULA.

=IF(original formula > 0, original formula)

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.