I'm having some problems getting the most frequent value I'm using a formula that works is the next one:

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

But in some column the data appears like this:

An error when the value is not occurring more than once I would like to add some condition but I've been trying with different formulas and I did't get better results some advices or formulas that I could use please?

CodePudding user response：

per column:

=INDEX(QUERY(A1:A9, 
 "select A,count(A) where A is not null group by A order by count(A) desc"), 2, 1)

per range:

=INDEX(QUERY(FLATTEN(A1:G9), 
"select Col1,count(Col1) where Col1 is not null group by Col1 order by count(Col1) desc"), 2, 1)

CodePudding user response：

You can also the formula below:

=ARRAYFORMULA(INDEX(A1:A10,MATCH(MAX(COUNTIF(A1:A10,A1:A10)),COUNTIF(A1:A10,A1:A10),0)))

And later drag it across the row:

The notable functions used for this are the following:

• COUNTIF which will return the prevalent number of occurrences;

• MATCH in order to match the predominant choice;

• ARRAYFORMULA which enables the display of values returned from an array formula into multiple rows and/or columns and the use of non-array functions with arrays.

Reference

• COUNTIF;

• MATCH;

• ARRAYFORMULA.