Use countifs to count all projects with a quarter date and year from a date column
my current formula is =countifs({sheet reference}, ROUNDUP(MONTH(@cell)/3) = "4", {sheet reference}, Year(@cell) = "2022"
I have ensured the column from the reference is a date column, however I am given invalid data type as an error
Best Answer
-
Paul Newcome ✭✭✭✭✭✭It sounds like you may have blanks and/or non-date values in the date column. Try a couple of IFERRORS like so:
=countifs({sheet reference}, ROUNDUP(IFERROR(MONTH(@cell), 0)/3) = "4", {sheet reference},IFERROR(Year(@cell), 0)= "2022"
Answers
-
Paul Newcome ✭✭✭✭✭✭
It sounds like you may have blanks and/or non-date values in the date column. Try a couple of IFERRORS like so:
=countifs({sheet reference}, ROUNDUP(IFERROR(MONTH(@cell), 0)/3) = "4", {sheet reference},IFERROR(Year(@cell), 0)= "2022"
Help Article Resources
Categories
The formula below will get what you need. With Smartsheet, you have to convert % to decimal values in formulas. <\/p>
=IF([Participation %]@row = 0, \"Not Participating\", IF([Participation %]@row > 0.8, \"On Track\", IF(AND([Participation %]@row < 0.81, [Participation %]@row > 0), \"Needs Improvement\", \"Blank\")))<\/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":108795,"type":"question","name":"Error Using Max Function","excerpt":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220) However, this one works fine so I don't see why there would be an invalid column error on the first formula. =NETWORKDAYS([End Date]207, [Start…","snippet":"Having an issue with the Max formula. Appreciate any advice the community can provide. This formula is giving me an Invalid Column Error: =MAX([End Date]195:[End Date]220)…","categoryID":322,"dateInserted":"2023-08-10T15:42:27+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T19:20:09+00:00","insertUserID":147716,"insertUser":{"userID":147716,"name":"Laura Krylov ASGLLC","url":"https:\/\/community.smartsheet.com\/profile\/Laura%20Krylov%20ASGLLC","photoUrl":"https:\/\/lh3.googleusercontent.com\/a\/AATXAJzqP5l6RPvTnPZ_FElh_Gd3nK2OrCoGmkfTmo11=s96-c","dateLastActive":"2023-08-10T19:05:06+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-10T23:43:23+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":27,"score":null,"hot":3383379756,"url":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108795\/error-using-max-function","format":"Rich","lastPost":{"discussionID":108795,"commentID":390113,"name":"Re: Error Using Max Function","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390113#Comment_390113","dateInserted":"2023-08-10T19:20:09+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-10T23:43:23+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-10T17:50:34+00:00","dateAnswered":"2023-08-10T15:59:34+00:00","acceptedAnswers":[{"commentID":390050,"body":"
Is the column you are inserting the formula into formatted as a date 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":[]},{"discussionID":108786,"type":"question","name":"Can't Save Field Logic Updates in Dynamic View","excerpt":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It asks the user to select a Category and to deter using Issues\/Miscellaneous, I have added a dropdown column in the sheet for the user to confirm…","snippet":"Hi all, I've seen a few similar posts regarding this, but they were from a few years back so not sure if anything has changed. I want to add Field Logic to a field in our form. It…","categoryID":322,"dateInserted":"2023-08-10T14:14:23+00:00","dateUpdated":null,"dateLastComment":"2023-08-10T16:42:12+00:00","insertUserID":158227,"insertUser":{"userID":158227,"name":"BristolCVN","url":"https:\/\/community.smartsheet.com\/profile\/BristolCVN","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-10T15:49:46+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"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-10T19:35:44+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":19,"score":null,"hot":3383364395,"url":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108786\/cant-save-field-logic-updates-in-dynamic-view","format":"Rich","tagIDs":[443],"lastPost":{"discussionID":108786,"commentID":390063,"name":"Re: Can't Save Field Logic Updates in Dynamic View","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390063#Comment_390063","dateInserted":"2023-08-10T16:42:12+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-10T19:35:44+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\/8C0H47JEM9MT\/fieldlogic.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"fieldlogic.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-10T15:49:43+00:00","dateAnswered":"2023-08-10T14:51:23+00:00","acceptedAnswers":[{"commentID":390035,"body":"
Hi @BristolCVN<\/a> <\/p> 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"}]}],"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":[]}">
