# Count formula

Column A has a list of names (5000 rows). How do I calculate or what formula do I need to use to figure out which name appears the most.

thanks
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
Microsoft Excel ExpertCommented:
please use this formula to find the most frequent occurring value =INDEX(A2:A5000,MATCH(MAX(COUNTIF(A2:A5000,A2:A5000)),COUNTIF(A2:A5000,A2:A5000),0),1)

and to count most frequently occurring value use this formula =COUNTIF(A2:A5000,INDEX(A2:A5000,MATCH(MAX(COUNTIF(A2:A5000,A2:A5000)),COUNTIF(A2:A5000,A2:A5000),0)))

all formulas are array formula entered with special key stroke of CONTROL SHIFT ENTER

Book1.xlsx
Excel & VBA ExpertCommented:
You may try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
Why an Array Formula? That is because if the range A1:A5000 contains blanks, the below formula would work and will return the most occurred text in the range A1:A5000.
``````=INDEX(A1:A5000,MODE(IF(A1:A5000<>"",MATCH(A1:A5000,A1:A5000,0))))
``````

But if the range A1:A5000 doesn't contain any blank in it, you may simply use a normal formula (not an array formula) in that case to return the most occurred text in the range A1:A5000 and which is like this.....
``````=INDEX(A1:A5000,MODE(MATCH(A1:A5000,A1:A5000,0)))
``````

Hope this helps.

*******************************
Remember that an Array Formula is confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.  You will know the array is active when you see curly braces { } appear around your formula.  If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.  Press F2 on that cell and try again.

Experts Exchange Solution brought to you by