How to count number of projects in 2023 by current status
Hi,
I created a Metrics sheet that feeds a dashboard last year that counted the status of a variety of items and it worked great when there was just one year. This year, I now need to only count the items that are for 2023. In the main sheet, I have a column that has the year in a string format (it's a formula =RIGHT([Planned Delivery]@row, 4)) where Planned Delivery is a picklist of MMM YYYY.
My Project tracking sheet
My Metrics sheet
My original formula that works for counting all the projects:
=COUNT(COLLECT({Project ID}, {Project Status},[email protected]))
What I've tried:
=COUNTIFS(COLLECT({Project ID}, {BOW Year}, "2023", {Project Status},[email protected],))Result is #UNPARSEABLE
=COUNTIF(COLLECT({Project ID}, {Project Status},[email protected]), CONTAINS("2023", {BOW Year}))Result is 0
=COUNTIF(COLLECT({Project ID}, {Project Status},[email protected], {BOW Year}, "2023"), 1)Result is 0
Can someone please help me figure out what I need to do to update my metrics sheet to only include items flagged as 2023? Thanks!
Answers
-
Eric Law ✭✭✭✭✭@SamanthaHopeDuignamThis is a simple solution =COUNT(COLLECT({Project ID}, {Project Status},[email protected],{BOW Year}, =2023))
-
Thanks@Eric Law. I tried it, and it's returning 0 as the result... any thoughts?
-
Eric Law ✭✭✭✭✭
@SamanthaHopeDuignamTry swapping out=2023for"2023". I looks like your BOW Year is a string not an integer.
-
@Eric Lawstill results with 0I checked the ranges, they are correct.
-
Eric Law ✭✭✭✭✭
@SamanthaHopeDuignamCould you change the BOW Year to, instead of a string, be =YEAR([Planned Delivery]@row) and use the first formula I posted?
-
This worked - thanks@Eric Law!
Help Article Resources
Categories
Check out theFormula Handbook template!
I think this should work:<\/p>
=IF(COUNTIF(Referencia:Referencia, Referencia@row) > 1, 1)<\/p>
Have a great day.<\/p>
Matt<\/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":107888,"type":"question","name":"Filter distinct values only into new column","excerpt":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only give me the unique values of [(Cells Linked from TE Tracker)] From what I've seen, there is no direct function that can do this, but has anyone…","snippet":"Hello! I have a column that consists of rows with values, some that differ and some that repeat [(Cells Linked from TE Tracker)], I want the next column over \"Distinct\" to only…","categoryID":322,"dateInserted":"2023-07-20T22:29:01+00:00","dateUpdated":null,"dateLastComment":"2023-07-24T02:43:35+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-24T05:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":163466,"lastUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-24T05:56:02+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":7,"countViews":51,"score":null,"hot":3380062956,"url":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/107888\/filter-distinct-values-only-into-new-column","format":"Rich","lastPost":{"discussionID":107888,"commentID":386593,"name":"Re: Filter distinct values only into new column","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/386593#Comment_386593","dateInserted":"2023-07-24T02:43:35+00:00","insertUserID":163466,"insertUser":{"userID":163466,"name":"sawuzie","url":"https:\/\/community.smartsheet.com\/profile\/sawuzie","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-07-24T05:56:02+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-07-22T05:31:23+00:00","dateAnswered":"2023-07-21T02:06:24+00:00","acceptedAnswers":[{"commentID":386312,"body":"
