Rank Top 5 Versions

Options

Hi,

I need assistance in finding the top 10 major releases.

I have 8 sheets with the same structure. Each sheet represents data for a specific quarter, such as 23Q1, 23Q2, and so on, up to 24Q4.
Each row represents an open case with case details.
Each sheet has 25 columns and 10,000 to 17,000 rows of data.

Example of such sheet:

I would like to have a statistical summary per product, showing the top 10 major releases from the last 1, 4, and 6 quarters.

Expected Result for product A:

I have tried using a solution that counts each version and then ranks them, but I encountered an error due to too many functions.

Count Column:
=COUNTIF([Major Software Release]:[Major Software Release], [Major Software Release]@row)
Rank Column:
=RANKEQ(Count@row, Count:Count, 0)

Could you please assist me in finding an alternative solution?

Thank you.

Answers

  • Mark.poole
    Mark.poole ✭✭✭✭✭✭
    Options

    @Igor.Trofimenko

    The only option I see since your running into the issue of cell ref. Is to run an automation to copy the sheet to another sheet. "This will copy the values and not the formulas" At which point you will be able to run the formulas you need.

    If you found this comment helpful. Please respond with any of the buttons below. Awesome🖤, Insightful💡, Upvote⬆️, or accepted answer. Not only will this help others searching for the same answer, but help me as well. Thank you.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!