Google Sheets most frequent and just one value-CodePudding

I'm trying to count the number of most frequent text in a column, I have this formula that I used before in excel...

=INDEX(E9:E18,MODE(IF((E9:E18<>"")*ISNA(MATCH(E9:E18,$B$1:$B1,0)),MATCH(E9:E18,E9:E18,0))))

This works, but when I get some repited value shows #N/A Here is an capture from this I would like to know how can I resolve this I tried with

=INDEX(E9:E18,MODE(IF(AND(E9:E18<>"")*ISNA(MATCH(E9:E18,$B$1:$B1,0)),MATCH(E9:E18,E9:E18,0))))

But I get lost trying to put a condition like only one value = value Some help or code that I could use?

So this is my problem

CodePudding user response：

With Office 365: =INDEX( SORTBY(UNIQUE(A1:A10), COUNTIF( A1:A10, UNIQUE(A1:A10) ),-1), 1 )

Oops - i just saw the Google sheets edit.

For most frequent:

=INDEX( SORT(UNIQUE(A1:A10), COUNTIF( A1:A10, UNIQUE(A1:A10) ),0), 1 )

For Just One Value:

=FILTER(UNIQUE(A1:A10),COUNTIF(A1:A10,UNIQUE(A1:A10))=1)

CodePudding user response：

try:

=QUERY({A:A}; 
 "select Col1,count(Col1) 
  where Col1 is not null 
  group by Col1 
  label count(Col1)''")