Google sheets: multiple returns of frequent text

[rayted]

Hi guys

i HAVE SIMPLE GOOGLe sheet with data in 1 column

I am trying to determine the most frequent text and have the value returned. How do I change my formula to return the most frequent text if there is more than 1 FREquent text?

This is my existing formula: =ARRAYFORMULA(INDEX(B1:B17,MATCH(MAX(COUNTIF(B1:B40,B1:B40)),COUNTIF(B1:B40,B1:B40),0)))

Regards. Thank you

https://docs.google.com/spreadsheets...it?usp=sharing

Ideally i guess it should say, red, yellow

[hrlngrv]

Most frequent text:

E1:

Please Login or Register  to view this content.

Next most frequent text even if same number of instances:

E2:

Please Login or Register  to view this content.

Fill E2 down until it returns #N/A.

[rayted]

Hi hringv

thank you, is there a way to have this displayed as "yellow, red" thank you!

[hrlngrv]

Sorry. I was previously thinking like Excel rather than Google Sheets. This can be done more directly in Google Sheets using its ability to construct arrays.

Please Login or Register  to view this content.

Deconstructing that, unique(B1:B40) are the distinct items from B1:B40 in order of 1st appearance. countif(B1:B40,unique(B1:B40)) is the number of occurrences of each distinct item. The Google Sheets gem is {countif(B1:B40,unique(B1:B40)),unique(B1:B40)} which is a constructed array the 1st column of which are the number of occurrences of each distinct, and the 2nd column of which are the distinct items. The SORT call sorts that constructed array on its 1st column in descending order. The INDEX call removes the 1st column, leaving only the distinct items in order of most to least occurrences, and TEXTJOIN groups them into a comma-separated list in a single cell.