Stuck on IF/AND formula
Hi I am using the Summary area and trying to create a formula that will show me when my Finish date is greater than today's date AND the status is not "complete" then say Overdue and if false say None Overdue. I have messed around with this for half a day (and other related items) and I am too frustrated to try anymore so please help! Formulas are NOT my forte lol! What I have below is what I could come up with according to all the help guides and tutorials I could find.
=IF(AND([Finish]:[Finish]@row)>TODAY(0),(([Status]:[Status]@row)<>"Complete"),"Overdue","None Overdue")
I would also love a separate formula to create the Count of those past due for the same scenario - these are going in my dashboard and management doesn't want to see the physical overdue tasks, they just want to know if any exist and how many there are.
Thank you so much!
Best Answer
-
@Nwhite_73 Let's fix some syntax.
IF(AND(logical expression 1, logical expression 2), value if true, value if false)
When you want to reference a single column on a given row, you just need to use the name of the column with @row. You surround the column name with square brackets if the column name is more than one word or if it contains numbers. (ex. Status@row, [End Date]@row).
To use AND inside an IF, you list your logical expressions by themselves, separated by commas; be sure to close off the AND with an end parentheses.
Putting those rules into action, we get this formula:
=IF(AND(Finish@row >TODAY(0), Status@row <>"Complete"), "Overdue", "None Overdue")
In order to get a count for your metrics or in a summary row, use COUNTIFS. The syntax is:
=COUNTIFS(Criteria Range1, Criteria 1, Criteria Range 2, Criteria 2)
There is an implied AND in COUNTIFS. All criteria must be true for the row to be counted.
=COUNTIFS(Finish:Finish, @cell > TODAY(0), Status:Status, <> "Complete")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Answers
-
@Nwhite_73 Let's fix some syntax.
IF(AND(logical expression 1, logical expression 2), value if true, value if false)
When you want to reference a single column on a given row, you just need to use the name of the column with @row. You surround the column name with square brackets if the column name is more than one word or if it contains numbers. (ex. Status@row, [End Date]@row).
To use AND inside an IF, you list your logical expressions by themselves, separated by commas; be sure to close off the AND with an end parentheses.
Putting those rules into action, we get this formula:
=IF(AND(Finish@row >TODAY(0), Status@row <>"Complete"), "Overdue", "None Overdue")
In order to get a count for your metrics or in a summary row, use COUNTIFS. The syntax is:
=COUNTIFS(Criteria Range1, Criteria 1, Criteria Range 2, Criteria 2)
There is an implied AND in COUNTIFS. All criteria must be true for the row to be counted.
=COUNTIFS(Finish:Finish, @cell > TODAY(0), Status:Status, <> "Complete")
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
It's unusual to have the overdue condition comprised of a finish date greater than today's date and a status other than complete. (As an example, that means that if my target finish date is next week and the status is still in progress, it's overdue. Usually, it would be the other way around...if my target finish date was last week - less than today -- with an incomplete status, then it's overdue).
Assuming that you really do want the overdue status determined as you described, then I would suggest adding a backend column (perhaps called "Overdue") with the following column formula:
=IF(AND([Finish Date]@row > TODAY(), Status@row <> "Complete"), 1, 0)
In the sheet summary, you could place this formula (altering the text as desired):
=IF(COUNTIF(Overdue:Overdue, 1) > 0, "# Tasks Overdue = " + COUNTIF(Overdue:Overdue, 1), "None Overdue")
With the backend column in place, you'll also be able to use conditional formatting to highlight the finish date and status. Additionally, you could set alerts based on the backend column to alert team members of overdue tasks.
Hope this helps!
-
Thank you both so much I tried both of your recommendations and multiple attempts to change them slightly to see if works a different way and the first one to state "overdue" just says it is "Unparseable" and the second one says it is an Incorrect Argument Set...I was so excited to see your responses and still very grateful that you took the time. I might leave this as a lost cause lol! And Yes, Kelly, I did change to a less than - thank you for the catch!
-
OH, heads up I did get the Count to work I missed a comma (copied and pasted but somehow it got messed up, weird!), so yeah on that one!
-
Ha I changed it so in the sheet summary where I put the overdue Count, I just said
=IF([Overdue Task Count]# >= 1, "Yes", "No")
So did a work around by using the count - it works for me!!! Thank you Thank you and thank you again!
-
If you want to include a screenshot of your sheet (cover up any sensitive data), I can maybe offer some better help.
I may have misinterpreted your initial request. Are you saying that you want the Summary cell to say "Overdue" if there's at least one row in the sheet that's overdue, and "None Overdue" if there are no overdue rows? If that's case, you can use the COUNTIFS to make this happen:
=IF(COUNTIFS(Finish:Finish, @cell > TODAY(0), Status:Status, <> "Complete") > 0, "Overdue", "None Overdue")
The logic: if the count of rows that meet the criteria is greater than 0, set the summary cell to "Overdue"; otherwise, set the summary cell to "None Overdue".
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Yes that is where I ended up, Jeff, not intentionally, your original review was correct. I just had to change my approach a bit and it got me the same result. Again thank you so much, I feel like I wasted so much time yesterday trying to get this to work and now today it seemed so simple, I think sometimes it is just brain fatigue trying to work on a problem one directionally! :-)
-
👍️ Excellent! I've learned so much from seeing how other users approach their solutions.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
