How to create a conditional formula with date specifications?
How do I create a formula that counts the number of rows containing [STATUS] “Green” and have [DATE] = Today and [DATE] = Last Seven Days from Today. I do not want to capture rows that are older than seven days from today.
Answers
-
Paul Newcome ✭✭✭✭✭✭Try something along the lines of...
=COUNTIFS(Status:Status, "Green", Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))
-
I tried the formula but got error #UNPARSEABLE:
=COUNTIFS([RYG Status]:[RYG Status],"Green",[Date]:[Date],AND([Date]<= TODAY(),[Date]>= TODAY(-7)))
I tried another version of the formlula (changed [Date] to [Date]:[Date], but then got error #INVALID OPERATION:
=COUNTIFS([RYG Status]:[RYG Status], "Green", [Date]:[Date], AND([Date]:[Date]<= TODAY(), [Date]:[Date]>=TODAY(-7)))
Any advice?
-
Paul Newcome ✭✭✭✭✭✭
In the first formula... Inside of the AND function, you should be using "@cell" exactly as I have in my previous comment. Not [Date].
Here is the formula I provided updated to reflect the column names you listed above.
=COUNTIFS([RYG Status]:[RYG Status], "Green", Date:Date, AND(@cell <= TODAY(), @cell >= TODAY(-7)))
Try using that exactly as it is.
-
Great thanks that solved it!
-
Paul Newcome ✭✭✭✭✭✭
Happy to help.️
Please don't forget to mark the most appropriate response(s) as "helpful" so that others searching for a similar solution can know that one may be found here.
Help Article Resources
Categories
I hope you're well and safe!<\/p>
You must add\/change the Rule #1 name.<\/p>
Did that work\/help? <\/p>
I hope that helps!<\/p>
Be safe, and have a fantastic week!<\/p>
Best,<\/p>
Andrée Starå<\/strong><\/a> | Workflow Consultant \/ CEO @ WORK BOLD<\/strong><\/a><\/p> ✅Did my post(s) help or answer your question or solve your problem? Please support the Community by <\/em>marking it Insightful\/Vote Up, Awesome, or\/and as the accepted answer<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/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":443,"urlcode":"dynamic-view","name":"Dynamic View"}]},{"discussionID":108756,"type":"question","name":"Showing what quarters students are here from date ranges","excerpt":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date Quarter - I have this formula in quarter currently that pulls the quarter from whatever month the experience start date begins in =\"Q\" +…","snippet":"Hello - I am trying to build a report for my supervisor that shows what quarters students are here from two date ranges. My columns are Experience Start Date Experience End date…","categoryID":322,"dateInserted":"2023-08-09T19:29:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":164879,"lastUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":2,"countViews":31,"score":null,"hot":3383297176,"url":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108756\/showing-what-quarters-students-are-here-from-date-ranges","format":"Rich","lastPost":{"discussionID":108756,"commentID":390066,"name":"Re: Showing what quarters students are here from date ranges","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390066#Comment_390066","dateInserted":"2023-08-10T16:56:30+00:00","insertUserID":164879,"insertUser":{"userID":164879,"name":"Taylorbecker1123","title":"Education Admin Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Taylorbecker1123","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T16:53:37+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,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/MS2Q5K5FZJPC\/smartsheet-jpg.jpg","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Smartsheet.JPG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T16:56:13+00:00","dateAnswered":"2023-08-09T21:34:54+00:00","acceptedAnswers":[{"commentID":389920,"body":" Try this:<\/p> =\"Q\" + ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) + IF(ROUNDUP(MONTH([EXPERIENCE START DATE:]@row) \/ 3) <> ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \" - Q\" + ROUNDUP(MONTH([EXPERIENCE END DATE:]@row) \/ 3), \"//www.santa-greenland.com/community/discussion/67511/\")<\/p> Hope that works for ya!<\/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":108765,"type":"question","name":"Production Downtime Tracking - Crossing the Dateline","excerpt":"Thanks for taking the time to read this post. I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data processing. An issue i am currently dealing with is how to calculate the impact or \"Yield\" in minutes of line downtime as we cross from one day to…","snippet":"Thanks for taking the time to read this post. I am attempting to restructure my companies end-of-shift reporting system with smartsheet to facilitate better automated data…","categoryID":322,"dateInserted":"2023-08-09T21:31:26+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T13:35:07+00:00","insertUserID":148642,"insertUser":{"userID":148642,"name":"Isaac T.","url":"https:\/\/community.smartsheet.com\/profile\/Isaac%20T.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T13:46:22+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:09:20+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":35,"score":null,"hot":3383292993,"url":"https:\/\/community.smartsheet.com\/discussion\/108765\/production-downtime-tracking-crossing-the-dateline","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108765\/production-downtime-tracking-crossing-the-dateline","format":"Rich","lastPost":{"discussionID":108765,"commentID":390007,"name":"Re: Production Downtime Tracking - Crossing the Dateline","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390007#Comment_390007","dateInserted":"2023-08-10T13:35:07+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-10T17:09:20+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-10T13:47:12+00:00","dateAnswered":"2023-08-10T10:14:42+00:00","acceptedAnswers":[{"commentID":389977,"body":"
