Overall Project Status based on frequency?
Hi,
所以我有以下表设置的项目where each Group may have more than one project in different statuses:
The order of status are the following: 1) Started, 2) In Progress and 3) Completed. I am trying to set up a formula that will return the most frequently populated Status per unique Group and also looks to return the highest ranked status if it's stuck between two Status values. Something like this:
So for example, since Group 1 has more projects with "In Progress Status", I want "In Progress" to show up as Overall Status. And since Group 2 has only two projects in different status, I want to return the highest ranked status between the two, which is "Completed."
Is there a possible formula or workaround to do this?
Answers
-
Nick Korna ✭✭✭✭✭✭Hi@yh374,
This formula should do what you're after for the Overall Status:
=IF((AND(COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "Completed") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "In Progress")), (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "Completed") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "Started"))), NOT(ISBLANK([Unique group]@row)))), "Completed", IF((AND(COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "In Progress") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "Complete")), (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "In Progress") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, "Started"))), NOT(ISBLANK([Unique group]@row)))), "In Progress", IF(NOT(ISBLANK([Unique group]@row)), "Started", "")))
This is if everything is in one sheet; obviously if your summary is elsewhere then you would have cross sheet references instead and you can ditch the NOT(ISBLANK) portions of the formula.
Output:
Hope this helps - if you've any questions etc. then just ask!
-
yh374 ✭
This is awesome, thank you Nick! I do have another related question that I hope you can provide some insights on.
Say, if I were to add another column called Domain to further categorize the project tasks and I want to output the recurring value where it matches both the Unique DomainANDUnique Group. And let's assume the Status order is now (from lowest to highest): 1) Started, 2) In Progress and 3) Completed, Completed-Delayed OR Completed-Early (these three completed values are ranked equal to each other) 4) On Hold.
How would I go about returning the most frequent value in this set up?
Sample data:
Desired output:
-
Nick Korna ✭✭✭✭✭✭
@yh374, you absolutely can.
There are a number of changes that need making, but here's the (rather long!) formula which should work:
=如果(不是(ISBLANK([独特集团]@row)),如果(((清纯甜美TIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, @cell = "On Hold") >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, CONTAINS("Completed", @cell)), COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, @cell = "On Hold") >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "In Progress"), COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, @cell = "On Hold") >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "Started"))), "On Hold", IF((AND(COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, CONTAINS("Completed", @cell)) >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "In Progress"), COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, CONTAINS("Completed", @cell)) >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "Started"))), "Completed", IF(COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "In Progress") >= COUNTIFS(Group:Group, [Unique group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "Started"), "In Progress", "Started"))), "")
This checks "On Hold" crops up equal to/more than each of the other status possibilities and if not, the 'higher' one(s) can then be removed for the subsequent IF statements.
The ISBLANK section is just in case you're using the same sheet as the data, otherwise you end having rows with no unique group showing up with a status - if you're doing it elsewhere (bottom of the sheet or substituting cross sheet references) then it can be removed.
If you want to give this a try and let me know of any issues - hopefully it is fine as is!
-
yh374 ✭
The formula worked - thank you! However, when I tried adding onto the formula to accommodate for 7 different status, I kept getting an #UNPARSEABLE error. This was the new ranking of status (lowest to highest) I set up; edited for clarity:
1) P1 2) P2 3) (P3, Q, W) (these three values are ranked equal to each other here) 4) P4 5) P5 6) P6 7) P7
Data set:
Desired output:
I got the first row to work but the other rows kept giving me issues. Any tips are appreciated! Also thank you so much for walking me through these solutions, Nick. I truly appreciate your time and help.
-
Nick Korna ✭✭✭✭✭✭
Hi again@yh374
With this many statuses I would add some helper columns to count the totals to make the overall status calculation much less lengthy (I'm not sure if all the COUNTIFS would actually fit in a single cell):
Here the P1 column is:
=IF(AND(NOT(ISBLANK([Unique Group]@row)), NOT(ISBLANK([Unique Domain]@row))), COUNTIFS(Group:Group, [Unique Group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, "P1"), "")
Obviously for the other single Px columns just change the P1 to the relevant value.
For the P3, Q, W column the formula is:
=IF(AND(NOT(ISBLANK([Unique Group]@row)), NOT(ISBLANK([Unique Domain]@row))), COUNTIFS(Group:Group, [Unique Group]@row, Domain:Domain, [Unique Domain]@row, Status:Status, OR(@cell = "P3", @cell = "Q", @cell = "W")), "")
Overall status is then:
=IF(AND(NOT(ISBLANK([Unique Group]@row)), NOT(ISBLANK([Unique Domain]@row))), IF(AND([P1]@row >= [P2]@row, [P1]@row >= [P3, Q, W]@row, [P1]@row >= [P4]@row, [P1]@row >= [P5]@row, [P1]@row >= [P6]@row, [P1]@row >= [P7]@row), "P1", IF(AND([P2]@row >= [P3, Q, W]@row, [P2]@row >= [P4]@row, [P2]@row >= [P5]@row, [P2]@row >= [P6]@row, [P2]@row >= [P7]@row), "P2", IF(AND([P3, Q, W]@row >= [P4]@row, [P3, Q, W]@row >= [P5]@row, [P3, Q, W]@row >= [P6]@row, [P3, Q, W]@row >= [P7]@row), "P3", IF(AND([P4]@row >= [P5]@row, [P4]@row >= [P6]@row, [P4]@row >= [P7]@row), "P4", IF(AND([P5]@row >= [P6]@row, [P5]@row >= [P7]@row), "P5", IF([P6]@row >= [P7]@row, "P6", "P7")))))), "")
You can hide these helper columns if you want the sheet to look a bit neater, but this won't affect any formula outcomes.
I notice that using your sample data I get a different result for Group/Domain 2C to your desired output, but by the formula this is correct.
Hope this helps, let me know if you have any issues or yetmorevariables to add in!
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/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":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+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-27T02:16:35+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":26,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+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-27T02:16:35+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-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/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":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"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-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":25,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+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-26T17:06:33+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-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"
![https:\/\/us.v-cdn.net\/6031209\/uploads\/3WNKU4A9GPTX\/image.png<\/a><\/p>Hope this helps - if you've any questions etc. then just ask! 😊<\/span><\/p>","bodyRaw":"[{\"insert\":\"Hi \"},{\"insert\":{\"mention\":{\"name\":\"yh374\",\"userID\":158753}}},{\"insert\":\" ,\\nThis formula should do what you're after for the Overall Status:\\n=IF((AND(COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"Completed\\\") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"In Progress\\\")), (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"Completed\\\") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"Started\\\"))), NOT(ISBLANK([Unique group]@row)))), \\\"Completed\\\", IF((AND(COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"In Progress\\\") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"Complete\\\")), (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"In Progress\\\") >= (COUNTIFS(Group:Group, [Unique group]@row, Status:Status, \\\"Started\\\"))), NOT(ISBLANK([Unique group]@row)))), \\\"In Progress\\\", IF(NOT(ISBLANK([Unique group]@row)), \\\"Started\\\", \\\"\\\")))\\nThis is if everything is in one sheet; obviously if your summary is elsewhere then you would have cross sheet references instead and you can ditch the NOT(ISBLANK) portions of the formula.\\nOutput:\\n\"},{\"insert\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3WNKU4A9GPTX\\\/image.png\",\"attributes\":{\"link\":\"https:\\\/\\\/us.v-cdn.net\\\/6031209\\\/uploads\\\/3WNKU4A9GPTX\\\/image.png\"}},{\"insert\":\"\\n\"},{\"insert\":\"Hope this helps - if you've any questions etc. then just ask! \"},{\"insert\":{\"emoji\":{\"emojiChar\":\"😊\"}}},{\"insert\":\"\\n\"}]","format":"rich","dateInserted":"2023-02-27T11:42:39+00:00","insertUser":{"userID":151203,"name":"Nick Korna","url":"https:\/\/community.smartsheet.com\/profile\/Nick%20Korna","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-25T12:51:50+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"displayOptions":{"showUserLabel":false,"showCompactUserInfo":true,"showDiscussionLink":false,"showPostLink":false,"showCategoryLink":false,"renderFullContent":false,"expandByDefault":false},"url":"https:\/\/community.smartsheet.com\/discussion\/comment\/364478#Comment_364478","embedType":"quote"}"> https://community.smartsheet.com/discussion/comment/364478#Comment_364478](\"https:\/\/us.v-cdn.net\/6031209\/uploads\/3WNKU4A9GPTX\/image.png\"){kind=link}