I cant find the formula for "most used" ?

[slamanager]

Hi,

Is there a formula to show the highest frequency of text in a column.
I thought i had a formula but looking through my example sheet i must have done this before manually.

col A
t1
t1
t1
t2
t2
t2
t2

so the answer is 4 i dont need the actual col A ref or detail.

 

[T. Valko]

Hi,

Is there a formula to show the highest frequency of text in a column.
I thought i had a formula but looking through my example sheet i must have done this before manually.

col A
t1
t1
t1
t2
t2
t2
t2

so the answer is 4 i dont need the actual col A ref or detail.

Try this array formula**:

=MODE(MATCH(A2:A8,A2:A8,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

If there aren't any duplicates then you'll get an error.

 

[pgc01]

Hi

Try:

=MAX(COUNTIF(A2:A8,A2:A8))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER.

 

[slamanager]

Thats tops guys thanks for the prompt reply all working. On to my next issue I suppose.

 

[T. Valko]

Thats tops guys thanks for the prompt reply all working. On to my next issue I suppose.

You're welcome. Thanks for the feedback!

 

[MrBill11]

pgc01
What would need to be added to the "=max(countif(" formula if you wanted to know that t2 was most used?

 

[T. Valko]

pgc01
What would need to be added to the "=max(countif(" formula if you wanted to know that t2 was most used?

Like this...

Both formulas are array formulas**.

=INDEX(A2:A8,MODE(MATCH(A2:A8,A2:A8,0)))

=INDEX(A2:A8,MATCH(MAX(COUNTIF(A2:A8,A2:A8)),COUNTIF(A2:A8,A2:A8),0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

As you can see the MODE(MATCH version is much shorter.

 

[MrBill11]

T.Valco,

mode(match worked very well. I will pass that along.

Thank You

 

[T. Valko]

T.Valco,

mode(match worked very well. I will pass that along.

Thank You

You're welcome. Thanks for the feedback!