Long IF Statement Help

https://help.smartsheet.com/function/and

I think this is doing what you described. I may have misinterpreted your needs - we can tweak to make it correct.

I added a Status condition to your Red term as without it, eventually all your rows would turn Red as time marches forward. You only want the row to be Red if the row is not already completed. I made a guess at what would indicate Complete - edit this to make it accurate on your sheet.

I guessed you were really looking for non blanks rather than asterisks in some of the text fields. Some of the fields in that range are not Text fields so you cannot call out a specific text string. The ISTEXT does not designate a specific string - it verifies the value is text.

I believe there are some conditions that are not included in your list of criteria. I added a 'Gray' at the end of the nested IFs so that the dot would not be blank if an unnamed condition occurs. If you want a blank, remove the very last ,"Gray" It could be that my misinterpretation of your request allowed unnamed conditions to occur.

=IF([Form Submission Date]@row = TODAY(), "Gray", IF(AND(Status@row <> "Completed", [Deadline Date]@row <= TODAY(), [Nurse Intake Due Date]@row <= TODAY()), "Red", IF(AND([Deadline Date]@row > TODAY(), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>"") = COUNT([Litigation Manager]@row:[Add New Entry to Billing System]@row)), "Green", IF(AND([Deadline Date]@row > TODAY(), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>"") = COUNT([Litigation Manager]@row:[Add New Entry to Billing System]@row)), "Green", IF(AND([Deadline Date]@row < TODAY(), [Nurse Intake Due Date]@row > TODAY(3)), "Yellow", "Gray")))))

Here is information on the IF/AND function

Let me know what we need to tweak and we'll get this to work perfectly.

Kelly

Excellent,

Thank you @Kelly Moore, from what I can see this looks mostly good and I'm still working through it.

For my understanding could you please explain the logic behind the Green section with the COUNTIFS?

=IF(AND([Deadline Date]@row > TODAY(), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>"") = COUNT([Litigation Manager]@row:[Add New Entry to Billing System]@row)), "Green",

Thanks!

Certainly.

Looking at your formula, it appeared you were using asterisks in some of the columns in the range you listed. However, not all of the columns in that range are text fields, so you couldn't have an asterisk in them. I assumed you were really looking for a non-blank cell. I didn't want to hard code how many columns you were counting in that range in case you added/deleted columns within that range. The COUNTIFS formula allows you to specify criteria to count. In my COUNTIFS, it is looking to count any non blank cells in that range. The Count function just counts how many cells are in the range. That statement says IF the number of non blank cells (in this range) equals the number of cells (in this range) AND the deadline is greater than today, 'Green'.

I had a little trouble understanding what you needed on the Green. Help me interpret that, maybe just with words not formulas, and I'll try again.

Kelly

Hi @Kelly Moore,

Thank you that makes sense. Although when I try using only the block function of the Green, the status will become green regardless of the [Litigation Manager]@row:[Add New Entry to Billing System]@row section being filled with text.

=IF(AND([Intake Deadline]@row > TODAY(), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>"") = COUNT([Litigation Manager]@row:[Add New Entry to Billing System]@row)), "Green")

Sorry, I thought I had tested that part. Replace the Green with this

=IF(AND([Deadline Date]@row > TODAY(), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>"") = SUM(COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, <>""), COUNTIFS([Litigation Manager]@row:[Add New Entry to Billing System]@row, ""))), "Green")

In your text fields where you seem to place an asterisk - have you considered using a checkbox or flag field? This might make data entry easier, particularly if mobile devices are used?

Hmm, I am stumped I have pasted this in and am getting "UNPASRSEABLE".

I have considered using the checkbox, however I want to know who is completing these tasks, so without adding additional columns I figure it would be best to acknowledge completion if there is text in the cell.

Also, and sorry to through this on you as it might make things more complicat

@FreedsAdvisory

In the formula you just pasted:

1) Are all the column names colored?

2) Is the last parenthesis in the formula blue?

This formula is working in my test sheet

If you continue to get an UNPARSEABLE, please take a screenshot of the formula so I can see the colored text

Hey @Kelly Moore,

Thanks for your help once again, attached please find a screenshot of what I am seeing. Please note that I have changed the name of Litigation Email to "Nurse Intake Completed".

Screen Shot 2021-09-05 at 12.25.37 PM.png

Notice how the column names in your formula are not colored. We have an error in the column names. Do you have a [Deadline Date] column? I didn’t notice it in this screenshot

Double check the square brackets in the formula- are they all in the correct place. I didn’t notice any out of place- and they are very easy to overlook- I’ve almost gone blind looking for those errors in my formulas.

Ah, excellent that worked! Thank you so much!

Lastly, for the yellow portion

=IF(AND([Intake Deadline]@row < TODAY(), [Nurse Intake Deadline]@row > TODAY(3)), "Yellow", "Gray")

How am I able to piece together this function to where only workday days are counted as three additional days from the Nurse intake date? TODAY(3) seems to include weekends into the count.

https://help.smartsheet.com/function/networkdays

@FreedsAdvisory

IF(AND([Deadline Date]@row < TODAY(), NETWORKDAYS(TODAY(), [Nurse Intake Due Date]@row) > 3), "Yellow"

In the post above, when you said 'it worked', did you find a wrong column name in the formula? If yes, you may need to apply the same correction to the formula I'm suggesting now.

Also, in my very first post I mentioned there were missing conditions. What happens, for instance, if the Deadline date is greater than today - Or the Networkdays <3? The very last 'Gray' in the formula will set these non-stated conditions to a Gray status. Is that the color you want it to be? I suggest on each of the Red, Yellow and Green terms in the equation you go through each criteria and make it false (also try blank cells). What happens to the color then and is it what you expected? You may have additional IF clauses for us to add.

Kelly