Google sheets "MODE" function for non-numeric values

user_10727821 0
Viewed 961

I'm trying to write a Google Sheets function that does the same as the MODE function, but for non-numeric values: returning the most frequently occuring string in a specified range.

I've searched the entire internet, and everywhere it's being suggested to use

=index(G2:L2, match(max(countif(G2:L2; G2:L2)), countif(G2:L2;G2:L2), 0))

or similar. This however returns an #ERROR!, which kind of leads me to believe that the correct syntax must have changed somehow… The range values are not even being highlighted in orange, as it usually does.

google-sheets-query google-sheets-formula counting google-sheets match
2 Answers

try:

=INDEX(QUERY(A:A, "select A,count(A) group by A order by count(A) desc"), 2, 1)

0

user_56326290

Replacing all commas (,) with semicolons (;) fixed the issue. I didn't know there were language specific syntaxes to the function language.

=index(G2:L2;match(max(countif(G2:L2;G2:L2));countif(G2:L2;G2:L2); 0))
user_107278210
Related