Overall Project Status based on frequency?

yh374

Hi,

So I have the following sheet set up for projects 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?

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

https://community.smartsheet.com/discussion/comment/364478#Comment_364478

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 Domain AND Unique 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:

=IF(NOT(ISBLANK([Unique group]@row)), IF((AND(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, 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

https://community.smartsheet.com/discussion/comment/364678#Comment_364678

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 yet more variables to add in! 😄