Finding the most common text over a range of rows
Hi Smartsheet community! I'm looking for a way to identify the most common textbased errors across a specific range of rows in my sheet.
Here's the situation:
I have a Smartsheet containing columns with text data. The text are errors that my team finds during a quality review.
I want to identify the most frequently occurring text within a defined range of rows (e.g., CR1  SP27).
Ideally, I would like to:
Find the top 5 most common errors.
See the actual error text, not just the count.
I've considered these options:
COUNTIFS/INDEX/MATCH: I understand these formulas can count occurrences and retrieve values, but I'm unsure how to adapt them for this specific scenario.
What's the best formula or approach to achieve this?
Thank you in advance for your help!
Answers

Hello,
Smartsheet doesn't currently have an equivalent to Excel's MODE() function, but there is an approach in this post you could adapt to determine the most frequent errors. In brief, you would need a helper column to count the matches and then pulling the largest values with INDEX/MATCH. For instance:
 Helper Column Formula: =COUNTIF([Error Column]:[Error Column], [Error Column]@row)
 Top Answers Formula: =INDEX([Error Column]:[Error Column], MATCH(LARGE([Helper Column]:[Helper Column],1),[Helper Column]:[Helper Column], 0))
You would just replace the 1 in the LARGE() function with the subsequent values. If 2 errors appeared the same number of times and were in the top 5 you would see the first to appear twice. Again, you could solve for this with another helper column, this type using a RANK() formula of the count values and then pulling the top 5 rank values.
Hope this helps!
Best,
Zach Hall
Training Delivery Manager / Charter Communications
Help Article Resources
Categories
Check out the Formula Handbook template!