How to show most frequent status per unique groupings?

yh374 ✭✭✭
edited 02/25/23 in Formulas and Functions


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?

Thank you for any ideas and help!


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!