Count per month/year automatically
What formula do I use to count data per month/year and have it update automatically with new entries. How do I pull that rolling data for past 12 month data to dashboard
Answers
-
Bassam Khalil ✭✭✭✭✭✭
Hi @Lynn Collier
Hope you are fine, please find in the following the formula to count by month for November for example
=COUNTIFS([Column2]:[Column2], <=DATE(2020, 11, 30)) - COUNTIFS([Column2]:[Column2], <=DATE(2020, 11, 1))
and if you want count for 2020 you can use the formula
=COUNTIFS([Column2]:[Column2], <=DATE(2020, 12, 31)) - COUNTIFS([Column2]:[Column2], <=DATE(2020, 1, 1))
check the following screen shot for the sample
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Bassam Khalil ✭✭✭✭✭✭
Hi @Lynn Collier
Did my post(s) help or answer your question or solve your problem? Please help the Community bymarking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
☑️Are you satisfied with my answer to your question? Please help the Community by marking it as an( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"
-
Hi, Sorry for the delay in getting back to you. The first formula works so that I do not have to put in a filler column of first and last day of the month. Thank you Lynn
Help Article Resources
Categories
You can do this with a nested IF statement. As a basic example, if you had a dropdown with options A, B, C and wanted to assign them points values of 1, 2, 3 respectively, you would use the following formula in your points column:<\/p>
=IF([Radio selection]@row = \"A\", 1, IF([Radio selection]@row = \"B\", 2, IF([Radio selection]@row = \"C\", 3)))<\/p>
![\"image.png\"](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/8IO6V0046RPJ\/image.png\")
<\/img><\/a>\n <\/div>\n<\/div>\nIf you have multiple instances of this then you could then use a SUM formula to get the total at the end.<\/p>
Hopefully this points you in the right direction, but if you've more specifics or have any problems\/questions then just post!<\/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":111115,"type":"question","name":"Can Smartsheet automatically push a date on to a Thursday?","excerpt":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for Smartsheet to check whether an End Date (publication date) falls on a Thursday and for Smartsheet to automatically move the date along to the…","snippet":"Hello. I thought I’d found the answer to this in a forum a while back, but the answer on there didn’t work for me when I tried it – I’d like to know if there’s a code\/formula for…","categoryID":322,"dateInserted":"2023-10-03T14:07:53+00:00","dateUpdated":null,"dateLastComment":"2023-10-03T15:17:08+00:00","insertUserID":167869,"insertUser":{"userID":167869,"name":"Lee5","title":"Mr","url":"https:\/\/community.smartsheet.com\/profile\/Lee5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-10-03T21:08:50+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-10-03T20:18:34+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":26,"score":null,"hot":3392690701,"url":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/111115\/can-smartsheet-automatically-push-a-date-on-to-a-thursday","format":"Rich","tagIDs":[254,334],"lastPost":{"discussionID":111115,"commentID":398125,"name":"Re: Can Smartsheet automatically push a date on to a Thursday?","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/398125#Comment_398125","dateInserted":"2023-10-03T15:17:08+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-10-03T20:18:34+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-10-03T14:58:34+00:00","dateAnswered":"2023-10-03T14:21:50+00:00","acceptedAnswers":[{"commentID":398101,"body":"
