Finding the most common text over a range of rows

Anthony W

Hi Smartsheet community! I'm looking for a way to identify the most common text-based 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!

Zachary Hall

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!