@kgerholdt<\/a> ,<\/p>One way is to use a \"helper\" column. Setup another column titled something like \"Submitted Month\" and use the following formula: =MONTH([Submitted]@row)<\/p>
Then in another field you can use =COUNTIF([Submitted Month]:[Submitted Month], =6)<\/p>
You can do this for each month you want a count of.<\/p>
Hope this helps.<\/p>
Dave<\/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":108991,"type":"question","name":"RYG status set by % Complete and End Date","excerpt":"I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please help!!! Here's what I'm trying to accomplish: Green = If % complete is 100% or if end date is greater than today Yellow = If % complete is not…","snippet":"I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please…","categoryID":322,"dateInserted":"2023-08-15T18:30:55+00:00","dateUpdated":null,"dateLastComment":"2023-08-15T20:05:32+00:00","insertUserID":165137,"insertUser":{"userID":165137,"name":"dhoch","url":"https:\/\/community.smartsheet.com\/profile\/dhoch","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!07FQv_TRRH0!wVFS8KDr0Xs!FO5J7dYBxHv","dateLastActive":"2023-08-15T20:00:56+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":163506,"lastUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-15T20:38:28+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":8,"countViews":53,"score":null,"hot":3384258987,"url":"https:\/\/community.smartsheet.com\/discussion\/108991\/ryg-status-set-by-complete-and-end-date","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108991\/ryg-status-set-by-complete-and-end-date","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108991,"commentID":390772,"name":"Re: RYG status set by % Complete and End Date","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390772#Comment_390772","dateInserted":"2023-08-15T20:05:32+00:00","insertUserID":163506,"insertUser":{"userID":163506,"name":"JamesB","title":"IT Project Manager","url":"https:\/\/community.smartsheet.com\/profile\/JamesB","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/avatarstock\/nEQMY42MFGXWS.png","dateLastActive":"2023-08-15T20:38:28+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\/U2DYEDL6DO7T\/smartsheet.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"smartsheet.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-15T19:27:36+00:00","dateAnswered":"2023-08-15T19:22:58+00:00","acceptedAnswers":[{"commentID":390746,"body":"
Try this:<\/p>
=IF(OR([% Complete]@row = 1, [End Date]@row> TODAY(7)), \"Green\", IF([End Date]@row< TODAY(), \"Red\", \"Yellow\"))<\/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":108954,"type":"question","name":"COUNTIFS formula looking at date columns in a reference sheet","excerpt":"I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look at the number of violations per month. I also want to see the number of date violations that occurred per month. Date violations would be…","snippet":"I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look…","categoryID":322,"dateInserted":"2023-08-15T12:57:30+00:00","dateUpdated":null,"dateLastComment":"2023-08-15T19:04:51+00:00","insertUserID":161890,"insertUser":{"userID":161890,"name":"Jade Boring","title":"Project Coordinator","url":"https:\/\/community.smartsheet.com\/profile\/Jade%20Boring","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-15T20:08:59+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-15T20:43:49+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":32,"score":null,"hot":3384232341,"url":"https:\/\/community.smartsheet.com\/discussion\/108954\/countifs-formula-looking-at-date-columns-in-a-reference-sheet","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108954\/countifs-formula-looking-at-date-columns-in-a-reference-sheet","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":108954,"commentID":390729,"name":"Re: COUNTIFS formula looking at date columns in a reference sheet","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390729#Comment_390729","dateInserted":"2023-08-15T19:04:51+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-15T20:43:49+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\/37DZUYMBZBUD\/inspection-metrics-png.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"Inspection metrics.PNG"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-15T20:10:04+00:00","dateAnswered":"2023-08-15T13:04:02+00:00","acceptedAnswers":[{"commentID":390649,"body":"
You will need a helper column in the source sheet to indicate on each row where there is an issue then count (or sum depending on your indicator) how many rows are flagged in this helper column.<\/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"}]}],"initialPaging":{"nextURL":"https:\/\/community.smartsheet.com\/api\/v2\/discussions?page=2&categoryID=322&includeChildCategories=1&type%5B0%5D=Question&excludeHiddenCategories=1&sort=-hot&limit=3&expand%5B0%5D=all&expand%5B1%5D=-body&expand%5B2%5D=insertUser&expand%5B3%5D=lastUser&status=accepted","prevURL":null,"currentPage":1,"total":10000,"limit":3},"title":"Trending in Formulas and Functions ","subtitle":null,"description":null,"noCheckboxes":true,"containerOptions":[],"discussionOptions":[]}">
Trending in Formulas and Functions
Count per month based off created date Hello, I'm struggling to get the count of submissions per month. For the submission date I used the "created date" in the drop down options for this specific column. I am not getting any errors with my formula it's simply counting them as "0." For example, June should show a total of 4 submission, but my formula is…
RYG status set by % Complete and End Date I am using a Smartsheet template (Project Tracking Template) and it comes with RYGB statuses. I would like to only use RYG and adjust the formula but am failing miserably. Please help!!! Here's what I'm trying to accomplish: Green = If % complete is 100% or if end date is greater than today Yellow = If % complete is not…
COUNTIFS formula looking at date columns in a reference sheet I used the Inspection Tracking template set a base. I liked it as a starting point, and added expiration dates. Looking at the metrics sheet provided, it uses this formula to look at the number of violations per month. I also want to see the number of date violations that occurred per month. Date violations would be…