2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
bruno
  • 369
  • 2
  • 15

2 Answers2

2

try:

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

0

player0
  • 124,011
  • 12
  • 67
  • 124
  • it's weird, I'm getting an error when copy-pasting the exact same code… https://docs.google.com/spreadsheets/d/1DzphzS1bLc5U_4tUCc3GtwGi91cI4AfLCcpLu5rwrmY/edit?usp=sharing – bruno Jul 19 '20 at 21:51
  • 1
    use: `=INDEX(QUERY(A:A; "select A,count(A) group by A order by count(A) desc"); 2; 1)` – player0 Jul 19 '20 at 21:53
  • ok great, this is working! is it possible to change the function to work on a different range (in my case, A2:D2)? – bruno Jul 19 '20 at 21:55
  • I'm also wondering why it needs a semicolon for me, but a comma seems to be working for you… – bruno Jul 19 '20 at 21:56
  • wow, okay, thanks for pointing this out! changing all commas to semicolons in the function from my question has resolved the issue… do you have any idea why it needs a different sign? is this an internationalization thing? – bruno Jul 19 '20 at 21:58
  • 1
    semicolon for you because you use German language in your spreadsheet settings – player0 Jul 19 '20 at 22:02
  • for A2:D2 use: `=INDEX(QUERY(TRANSPOSE(A2:D2); "select Col1,count(Col1) group by Col1 order by count(Col1) desc"); 2; 1)` – player0 Jul 19 '20 at 22:04
0

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))
bruno
  • 369
  • 2
  • 15