'Most Common' Formula


I am trying to create metrics on tracking the versions of an applications we are developing, currently I am attempting to find which version is most successful after it has been run through a series of criteria.


Build 30 - Success

Build 31- Success

Build 31 - Failure

Build 32 - Success

The Application versions will continually be increasing as we roll out more changes and features, so pre-defined selections are not possible. Any ideas?

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. So on your source sheet, you are gong to want to add in a text/number column (can later be hidden) that contains the following on all rows:

    =COUNTIFS([Build version]:[Build version], [Build version]@row, [Successful/Unsuccessful]:[Successful/Unsuccessful], "Successful")

    Then on your metrics sheet you would use a formula such as

    =JOIN(DISTINCT(COLLECT({Source Sheet Build version column}, {Source Sheet Helper column}, @cell = MAX({Source Sheet Helper column})), ", ")

    Using the appropriate method for creating cross sheet references, this will pull together all Build Versions that have the most number of successes.

    So if Build 1 had 30 and Build 2 had 29, then the formula on your metrics sheet would just show "Build 1", but if there were multiple builds tied for the most such as Build 1 has 30 and Build 2 has 30, it will show as "Build 1, Build 2"


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!