Count and then Rank multiple text values in same Cell in one Column

Tiffany Castro

I have been scouring the community page looking for help with this question and plan to set up a Pro Desk appointment, but they don't have any availability until next week so any help is appreciated.

I have a form collecting data into a sheet and then a separate calculations sheet. I am trying to 'count' within a column the number of times a topic was selected, and then sort/rank 1st, 2nd, 3rd picks. My problem is that there are multiple text values in the same cell.

The formula I am using only takes the first topic selected and doesn't account for the other topics selected in that cell.

=COUNTIF({Safety Training Topics - Flight Safety Categories}, "Other")

Screenshot of Form Data Column:

Screenshot of Results:

Matt Lynn ACT

Try this:

=COUNTIF({Safety Training Topics - Flight Safety Categories}, HAS(@cell,"Other"))

Matt Lynn ACT

Try this:

=COUNTIF({Safety Training Topics - Flight Safety Categories}, HAS(@cell,"Other"))

Matt Lynn ACT

Technically (assuming that works for you), you can improve the formula by replacing: "other" with Primary@row. (Assuming your primary column is still named "Primary". Then you could make that a column formula.

Tiffany Castro

The formula worked to get the count, THANK YOU! How do I apply it to the rest of the topics without typing each one out??

Tiffany Castro

ALSO - Once I get my results, how would I rank/sort them?

Matt Lynn ACT

So if you replace the "Other" part of your formula with a reference to the primary cell at that row, then either make it a column formula or copy/paste it would work for all the other rows.

As far as the rank, you'd want another column for that using either the RankAvg() or RankEQ() formula (prob the EQ). It would look something like :
=rankeq(count@row,countcolumn:countcolumn) Make this a column formula or a copy/paste on the rows you need it on.

Replace count@row and countcolumn:countcolumn with whatever your column names are.

Tiffany Castro

This is the formula I used to get 1st…

=INDEX({Safety Training Topics - Hot Topics Range 2}, MATCH(LARGE([Count Results]2:[Count Results]4, 1), [Count Results]2:[Count Results]4, 0))

And this is what I used to get 2nd…

=INDEX({Safety Training Topics - Hot Topics Range 2}, MATCH(LARGE([Count Results]2:[Count Results]4, 2), [Count Results]2:[Count Results]4, 0))

But it doesn't work for 3rd? What am I doing wrong