Do an If statement only if a cell is not blank

Automations 1
Automations 1 ✭✭✭✭✭
edited 09/09/20 in Formulas and Functions

Hi Community,

I have a list of names and I'm trying to keep track of different Pension sources they may have and the amount of each pension. Being that everyone on my list has a different amount of pensions, the formula counts to see how many pension sources are filled in. If that count equals the count of "amounts" that are filled in, we know we have all the information.


(See my previous question for info on writing such a formula: https://community.smartsheet.com/discussion/70493/check-off-completed-if-items-collected-with-different-number-of-items-needed-to-be-collected#latest.)


My formula reads as follows:

=IF(COUNTIF([Source I]@row, <>"") + COUNTIF([Source II]@row, <>"") + COUNTIF([Source III]@row, <>"") + COUNTIF([Source IV]@row, <>"") = COUNT([Amount I]@row, [Amount II]@row, [Amount III]@row, [Amount IV]@row), "Have all items", "Missing Items")


The problem is that when a new name is added to the list the formula populates as "Have all Items". Because the count of "sources" equals 0 because no information was put in yet, and the count of "amounts" is also zero.


What I'm looking for, I believe, is an if statement inside an if statement (If the first source is put in, then do this if statement, otherwise don't do the if statement yet.)! Or some sort of trigger to only start the if statement once the first Source is put in.

Best Answer

Answers

  • Automations 1
    Automations 1 ✭✭✭✭✭

    Thanks, that works! I original tried a nested if statement. However, I put the formula by "true" and not by "false".

  • David Tutwiler
    David Tutwiler Overachievers Alumni

    Ah! Makes perfect sense. Glad it's working now.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!