Long IF Statement Help

FreedsAdvisory

Hello,

I am attempting to create an IF statement on a Red, Yellow, Green, Grey icon text type with multiple factors. Below please find the inputs listed.

1. If new entry then Gray (COMPLETED) =IF([Form Submission Date]@row = TODAY(), "Gray")
2. If hard deadline has passed and nurse intake has passed then Red (COMPLETED, but can't figure how to join with AND statement?) =IF([Deadline Date]@row <= TODAY(), "Red") =IF([Nurse Intake Due Date]@row <= TODAY(), "Red")
3. If a range of rows has text in them and deadline is not in the passed then Green (Deadline portion completed, can't find how to have any input of text determine an outcome, here are my attempts) =IF([Litigation Manager]@row:[Add New Entry to Billing System]@row = ISTEXT("*"), "Green") =IF([Folders Created]@row = "*", "Green")
4. If Due Date has passed but nurse intake is greater than three days then Yellow (STUMPTED) =IF([Deadline Date]@row >= TODAY() + [Nurse Intake Due Date]@row , "Yellow")

All of these IF statements need to be combined into one column Column formula, if it may be better to use an automation rather than a formula I am happy with that as well.

Below please find a picture of my SmartSheet for column name references.

Thank you to whoever may help me take this on.

Kelly Moore

Hey @FreedsAdvisory

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

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

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

Kelly

FreedsAdvisory

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!

Kelly Moore

Hey @FreedsAdvisory

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

FreedsAdvisory

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

Kelly Moore

Hey @FreedsAdvisory

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?

FreedsAdvisory

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.

FreedsAdvisory

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

Kelly Moore

@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(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")

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

FreedsAdvisory

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".

=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")

Kelly Moore

Hey @FreedsAdvisory

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.

FreedsAdvisory

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.

Kelly Moore

@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.

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

Kelly

FreedsAdvisory

Thank you @Kelly Moore, really appreciate the help. We are good!