excel - Find most frequently occuring text in range that consist of multiple rows and colums

Question

My question is closely related to the following topics:

• Excel: Find most frequent occurring value in a range?
• https://exceljet.net/formula/most-frequently-occurring-text

I want to retrieve the text that occurs the most frequent within my range. The problem is that the range consists of multiple rows and/or columns. In the case of a single row / single column, the Excel formula is

=INDEX(D2:D9,MODE(MATCH(D2:D9,D2:D9,0)))

I know that this formula is correct because I get "Inter" as output in cell N2. This is indeed the correct output if I want the most frequent term of Range("D2:D9").

However, I want to find the term #1 of Range("D2:E3"). Incorporating this range in the formula, gives an error. See attachment.

Summarized, what is the correct Excel-formula that shows me the most frequent text of a range that consists of multiple columns and rows? Thank you

标签： excel

Answer 1

=INDIRECT(TEXT(MMULT(CHOOSE({1,2},ROW(MyRange),COLUMN(MyRange))+MOD(ROUNDUP(MATCH(1,0/FREQUENCY(0,1/(1+COUNTIF(MyRange,MyRange))))/COLUMNS(MyRange)^{1,0},0)-1,COLUMNS(MyRange)*ROWS(MyRange)^{1,0}),10^{5;0}),"R0C00000"),0)

Replace MyRange as required.

Note that, if you are not using an English-language version of Excel, parts of the above may require amending (the separators within the array constants - {1,2}, {1,0} and {5;0} - and the part "R0C00000" being two such examples).

Edit: the above is overkill; we can use simply (with CTRL+SHIFT+ENTER):

=INDIRECT(TEXT(MIN(IF(COUNTIF(Rng,Rng)=MAX(COUNTIF(Rng,Rng)),10^5*ROW(Rng)+COLUMN(Rng))),"R0C00000"),0)

Regards