formula to count text entries and return text with most entries

[Reppy]

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

 

[Ron Rosenfeld]

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

If Rng is the range containing your strings, then:

This formula must be **array-entered**:

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

 

[Reppy]

If Rng is the range containing your strings, then:

This formula must be **array-entered**:

=INDEX(Rng,MATCH(MAX(COUNTIF(Rng,Rng)),COUNTIF(Rng,Rng),0))

----------------------------------------

To **array-enter** a formula, after entering
the formula into the cell or formula bar, hold down
<ctrl><shift> while hitting <enter>. If you did this
correctly, Excel will place braces {...} around the formula.

Hello Ron

I must be missing something as when i follow your formula i get the messageof too few arguments. In your formula you use 'rng' twice in brackets. I actually only have one column to apply formula to. e.g. P23300 where all responses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing?

Cheers

Reppy

 

[Reppy]

H

Hello Ron

I must be missing something as when i follow your formula i get the message of too few arguments. In your formula you use 'rng' twice in brackets. Iactually only have one column to apply formula to. e.g. P23300 where allresponses are from a drop down list. There are five choices and i m looking for the most frequent response. Sorry to be a bother but can you tell me what i am missing?

Cheers

Reppy

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

 

[Ron Rosenfeld]

Hello Ron

I have worked it out and it works great. thanks heaps.

Reppy

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

 

[Reppy]

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would have guessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

Yep Ron - a typo. Sometimes i am sure that the fingers have a mind of there own. It was not till i left and returned later and saw the typo.

Cheers and thanks again, it will help me a great deal.

 

[Reppy]

Glad to help. Thanks for the feedback.

Out of curiousity, what was the problem with the first try. I would haveguessed a formula typo. And yes, Rng needed to be used multiple times in the formula.

Ron

I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have theformula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go.

Cheers

Reppy

 

[Don Guillett]

Hi all

I need to create a formula that will look down a column of text (e.g. apple, apple, apple, pear, orange) and not just count but return the actual entry that has been entered most -above would obviously be "apple"

I believe i would not to create an array formula but most formulas seem to relate to numbers or want to convert text to numbers. I just want the formula to display the word entered most in the relevant column.

Regards to all

Reppy

modify for column instead of row and enter using CSE Ctrl Shift Enter

=INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)"

or

=INDEX(D22500,MATCH(MAX(COUNTIF(D22500,D22500)),COUNTIF(D22500,D22500),0))

 

[Reppy]

modify for column instead of row and enter using CSE Ctrl Shift Enter

=INDEX(B2:K2,MATCH(COUNTIF(B2:K2,B2:K2), COUNTIF(B2:K2,B2:K2),0)) & " ("&COUNTIF(B2:K2,B2:K2)&" times)"

or

=INDEX(D22500,MATCH(MAX(COUNTIF(D22500,D22500)),COUNTIF(D22500,D22500),0))

Hello Don

Thanks heaps that worked great. Yourself and Ron are legends. Thanks guys this has been of immense help!!

Regards

Reppy

 

[Ron Rosenfeld]

I have one more question - can i add more to that formula so that it will ignore blank cells. By this i mean i want to apply the formula so that it will cover cells that are yet to be entered with data. For example i have the formula to cover A1:A100 but to date i only have date up to A50. If not possible then i will amend formula as I go.

If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data).

For example: Define Name
Name: Rng
Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A))

If your data is not like that, let me know, and we can come up with something else.

 

[Reppy]

If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data).

For example: Define Name
Name: Rng
Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A))

If your data is not like that, let me know, and we can come up with something else.

Hello Ron

Just logged on and saw your reply. I think this will work as well. I am going to have a look at it and give it a go tomorrow and see how this variesto the formula suggested by Don. It should work as there are no intervening blank cells. The blanks are just further down the list waiting for more data as i add it. This formula is somewhat shorter. I also was not aware that you could apply offset in this manner. There certainly is so much to learn.

Your knowledge is immense and greatly appreciated.

Cheers

Reppy

 

[Reppy]

If your entries are the only thing in column A, and if there are no intervening blank cells, you could use a dynamic name (one that adjusts depending on the extent of the data).

For example: Define Name
Name: Rng
Refers to: =OFFSET($A$1,0,0,COUNTA($A:$A))

If your data is not like that, let me know, and we can come up with something else.

Hello Ron

Just wanted to let you know that this formula also works well. As it is somewhat shorter than the previous ones i might use this one. It is quite amazing he we arrive at the same answer with varying formulas.

Thanks again

Reppy

 

[Ron Rosenfeld]

Hello Ron

Just wanted to let you know that this formula also works well. As it is somewhat shorter than the previous ones i might use this one. It is quite amazing he we arrive at the same answer with varying formulas.

Thanks again

Reppy

You're welcome. Thanks for the feedback.