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!
Best Answer

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

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))
Help Article Resources
Categories
Check out the Formula Handbook template!