determine which cell is the max count
 

Hello guys, pls. help.

Column A
dog
cat
cat
dog
dog

I would like to know which animal in the column A is max. or is min.
the max. is dog.
the min. is cat.

What is the formula to use?

determine which cell is the max count
 

one approach you could try. I assume you do not want empty/blank cells
considered?

=INDEX(A1:A10,MODE(IF(A1:A10<"",MATCH(A1:A10&"",A 1:A10&"",0))))

array entered using Ctrl+Shift+Enter. of course it will only find the first
one in case of a tie.

"Kim" wrote:

Hello guys, pls. help.

Column A
dog
cat
cat
dog
dog

I would like to know which animal in the column A is max. or is min.
the max. is dog.
the min. is cat.

What is the formula to use?

determine which cell is the max count
 

overlooked the second part of your question - finding the one with the fewest
entries. it's a bit longer, but I think this will work

=INDEX(A1:A10,MATCH(SMALL(IF(A1:A10<"",COUNTIF(A1 :A10,A1:A10&"")),1),IF(A1:A10<"",COUNTIF(A1:A10,A 1:A10&"")),0))


"JMB" wrote:

one approach you could try. I assume you do not want empty/blank cells
considered?

=INDEX(A1:A10,MODE(IF(A1:A10<"",MATCH(A1:A10&"",A 1:A10&"",0))))

array entered using Ctrl+Shift+Enter. of course it will only find the first
one in case of a tie.

"Kim" wrote:

Hello guys, pls. help.

Column A
dog
cat
cat
dog
dog

I would like to know which animal in the column A is max. or is min.
the max. is dog.
the min. is cat.

What is the formula to use?

determine which cell is the max count
 

also array entered with Ctrl+Shift+Enter.

"JMB" wrote:

overlooked the second part of your question - finding the one with the fewest
entries. it's a bit longer, but I think this will work

=INDEX(A1:A10,MATCH(SMALL(IF(A1:A10<"",COUNTIF(A1 :A10,A1:A10&"")),1),IF(A1:A10<"",COUNTIF(A1:A10,A 1:A10&"")),0))


"JMB" wrote:

one approach you could try. I assume you do not want empty/blank cells
considered?

=INDEX(A1:A10,MODE(IF(A1:A10<"",MATCH(A1:A10&"",A 1:A10&"",0))))

array entered using Ctrl+Shift+Enter. of course it will only find the first
one in case of a tie.

"Kim" wrote:

Hello guys, pls. help.

Column A
dog
cat
cat
dog
dog

I would like to know which animal in the column A is max. or is min.
the max. is dog.
the min. is cat.

What is the formula to use?

determine which cell is the max count
 

Thanks JMB,
I can solve the problem now.

"JMB" wrote:

also array entered with Ctrl+Shift+Enter.

"JMB" wrote:

overlooked the second part of your question - finding the one with the fewest
entries. it's a bit longer, but I think this will work

=INDEX(A1:A10,MATCH(SMALL(IF(A1:A10<"",COUNTIF(A1 :A10,A1:A10&"")),1),IF(A1:A10<"",COUNTIF(A1:A10,A 1:A10&"")),0))


"JMB" wrote:

one approach you could try. I assume you do not want empty/blank cells
considered?

=INDEX(A1:A10,MODE(IF(A1:A10<"",MATCH(A1:A10&"",A 1:A10&"",0))))

array entered using Ctrl+Shift+Enter. of course it will only find the first
one in case of a tie.

"Kim" wrote:

Hello guys, pls. help.

Column A
dog
cat
cat
dog
dog

I would like to know which animal in the column A is max. or is min.
the max. is dog.
the min. is cat.

What is the formula to use?