Most Common Text and Ignore blank cells within the range

[Rumpkin]

I use the following formula to determine the most common text in a single column range.
I need it to ignore any blank cells.

=INDEX(M11:M35,MATCH(MAX(COUNTIF(M11:M35,M11:M35)),COUNTIF(M11:M35,M11:M35),0))

Best Regards

 

[FDibbins]

OK, so is that working for you, or not?
Keep in mind that INDEX will return ONE answer that it finds at an intersection of 1 column and 1 row

 

[Rumpkin]

It is working correctly as long as 7 of the cells in the range (25 cells/rows) have the same answer or entry.
The result should be the one answer.
This range will only include 1 column but may include from 3 to over 100 rows.
What this formula will not do is ignore the blank cells in the range.
The cells in the range [the formula] evaluates may or may not have a values depending on another formula.
I need this formula to give me the result of the most common text within the range but ignore the blank cells.
Thanks!!

 

[Tetra201]

Is this what you need?

=INDEX(M11:M35,MATCH(MAX(IF(M11:M35<>"",COUNTIF(M11:M35,M11:M35))),COUNTIF(M11:M35,M11:M35),0))

 

[Rumpkin]

I thought that would/should work but it does not.
It is still indicating the most common is a blank.
What's curious is that once I have at least 7 of the same entries it
will return the the result to be that text.

I Appreciate your effort!!

 

[Aladin Akyurek]

I use the following formula to determine the most common text in a single column range.
I need it to ignore any blank cells.

=INDEX(M11:M35,MATCH(MAX(COUNTIF(M11:M35,M11:M35)),COUNTIF(M11:M35,M11:M35),0))

Best Regards

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

M​

[/td][td]

N​

[/td][td]

O​

[/td][/tr][tr][td]

10​

[/td][td] TEXT[/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

11​

[/td][td] lion[/td][td][/td][td]

2​

[/td][/tr]
[tr][td]

12​

[/td][td] jaguar[/td][td][/td][td]MOST FREQ[/td][/tr]
[tr][td]

13​

[/td][td] jaguar[/td][td][/td][td] lion[/td][/tr]
[tr][td]

14​

[/td][td] lion[/td][td][/td][td] jaguar[/td][/tr]
[tr][td]

15​

[/td][td] hyena[/td][td][/td][td] [/td][/tr]
[tr][td]

16​

[/td][td] [/td][td][/td][td] [/td][/tr]
[tr][td]

17​

[/td][td] wolf[/td][td][/td][td][/td][/tr]
[/table]

In O10 control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH(M11:M35,M11:M35,0)),ROW($M$11:$M$35)-ROW($M$11)+1))

In O11 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH($M$11:$M$35,$M$11:$M$35,0)),ROW($M$11:$M$35)-ROW($M$11)+1)=$O10,1))

In O13 control+shift+enter and copy down:

=IF(ROWS($O$13:O13)>$O$11,"",INDEX($M$11:$M$35,SMALL(IF(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH($M$11:$M$35,$M$11:$M$35,0)),ROW($M$11:$M$357)-ROW($M$11)+1)=$O10,ROW($M$11:$M$35)-ROW($M$11)+1),ROWS($O$13:O13))))

 

[Tetra201]

I thought that would/should work but it does not.
It is still indicating the most common is a blank...

What do you have in your "blank" cells?

 

[Rumpkin]

One is always nothing, sometimes two have nothing. The others always have a formula.
The reply from Aladin Akyurek
will not work for this case as the result of the formula determines the most common text and cannot us another column/cell as a slave.
Thanks, though.
The 28% (7:25) proves true through all quantities of rows/cells.

 

[Aladin Akyurek]

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

M​

[/TD]
[TD]

N​

[/TD]
[TD]

O​

[/TD]
[/TR]
[TR]
[TD]

10​

[/TD]
[TD] TEXT[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

11​

[/TD]
[TD] lion[/TD]
[TD][/TD]
[TD]

2​

[/TD]
[/TR]
[TR]
[TD]

12​

[/TD]
[TD] jaguar[/TD]
[TD][/TD]
[TD]MOST FREQ[/TD]
[/TR]
[TR]
[TD]

13​

[/TD]
[TD] jaguar[/TD]
[TD][/TD]
[TD] lion[/TD]
[/TR]
[TR]
[TD]

14​

[/TD]
[TD] lion[/TD]
[TD][/TD]
[TD] jaguar[/TD]
[/TR]
[TR]
[TD]

15​

[/TD]
[TD] hyena[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

16​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

17​

[/TD]
[TD] wolf[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


In O10 control+shift+enter, not just enter:

=MAX(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH(M11:M35,M11:M35,0)),ROW($M$11:$M$35)-ROW($M$11)+1))

In O11 control+shift+enter:

=SUM(IF(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH($M$11:$M$35,$M$11:$M$35,0)),ROW($M$11:$M$35)-ROW($M$11)+1)=$O10,1))

In O13 control+shift+enter and copy down:

=IF(ROWS($O$13:O13)>$O$11,"",INDEX($M$11:$M$35,SMALL(IF(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH($M$11:$M$35,$M$11:$M$35,0)),ROW($M$11:$M$357)-ROW($M$11)+1)=$O10,ROW($M$11:$M$35)-ROW($M$11)+1),ROWS($O$13:O13))))

Correction involving the formula in O13...

=IF(ROWS($O$13:O13)>$O$11,"",INDEX($M$11:$M$35,SMALL(IF(FREQUENCY(IF(1-($M$11:$M$35=""),MATCH($M$11:$M$35,$M$11:$M$35,0)),ROW($M$11:$M$35)-ROW($M$11)+1)=$O$10,ROW($M$11:$M$35)-ROW($M$11)+1),ROWS($O$13:O13))))

which must be confirmed with control+shift+enter and copied down.

 

[Tetra201]

One is always nothing, sometimes two have nothing. The others always have a formula...

It is obvious. What kind of formula?