Long IF Statement Help
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.
- If new entry then Gray (COMPLETED) =IF([Form Submission Date]@row = TODAY(), "Gray")
- 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")
- 如果一系列行文本我的最后期限s 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")
- 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.
Answers
-
Kelly Moore ✭✭✭✭✭✭
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!
-
Kelly Moore ✭✭✭✭✭✭
Certainly.
看着你的公式,看来你是强g 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 anynon blankcells 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")
-
Kelly Moore ✭✭✭✭✭✭
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
-
Kelly Moore ✭✭✭✭✭✭
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
-
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 ✭✭✭✭✭✭
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.
-
Kelly Moore ✭✭✭✭✭✭
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
-
Thank you@Kelly Moore, really appreciate the help. We are good!
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
