Do an If statement only if a cell is not blank

Automations 1

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.

David Tutwiler

It's just a nested If statement. You will put your original code as the False output of the first If Statement. It will look something like:

=IF([Amount I]@row=0,"",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"))

Essentially, the True statement of the Amount is 0 just returns an empty character (you could also return 0 for the same effect). If there is something in the Amount I then it solves your second IF Formula.

David Tutwiler

It's just a nested If statement. You will put your original code as the False output of the first If Statement. It will look something like:

=IF([Amount I]@row=0,"",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"))

Essentially, the True statement of the Amount is 0 just returns an empty character (you could also return 0 for the same effect). If there is something in the Amount I then it solves your second IF Formula.

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

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