Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Ben" wrote in message
...
thanks i think it got it.

"T. Valko" wrote:

Here's a small sample file that demonstrates this.

xBen.xls 17kb

http://cjoint.com/?bmsUtsNfjF

--
Biff
Microsoft Excel MVP


"Ben" wrote in message
...
thanks but when i insert the formula it returns Bert(only 4 points) as
the
answer when it should be Tom with 6 points. I'm using Excel 2003.

Not sure what the problem is?

"T. Valko" wrote:

Try this array formula** :

=INDEX(B1:J1,MATCH(MAX(SUMIF(B1:J1,B1:J1,B2:J2)),S UMIF(B1:J1,B1:J1,B2:J2),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.

Note that if more than one person has the highest total the formula
will
return the leftmost name with the highest total.

--
Biff
Microsoft Excel MVP


"Ben" wrote in message
...
That works if you have a static list of 5 people & treat all evenly.
I
have a
simliar problem but i have an open list of participants and also
want
to
assign points/weighting. Here is my data:

A B C D E F G H I
1 Tom Frank Sue Bert Gary Sue Tom Sue Bert
Pts 3 2 1 3 2 1 3 2 1

What i want to see is what is the most common name for event 1 (A1
to
I1)
but also apply weighting/points to the event. So if A1, D1 & G1
equals
3
points then B1, E1 & H1 = 2 points & C1, F1 & I1 - 1 points. So your
Index
formula works to show who is mentioned the most = Sue. But I want to
see
who
has the most points for the event = Tom.

Hope this is clear. Any help would be much appreciated as i have
been
going
through the lists manually and compiling.

"T. Valko" wrote:

Try this array formula** :

=INDEX(A2:A24,MODE(MATCH(A2:A24,A2:A24,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.

Copy across as needed.

--
Biff
Microsoft Excel MVP


"Darren" wrote in message
...
I have 3 columns of 24 cells headed 'gold' 'silver' and 'bronze'.
each
of
the
24 cells could have 1 of 5 answers depending on other
calculations
elsewhere
on the sheet. What function can I put into cell 25 of each column
to
count
to
most common name? Or to put it another way, to see who won most
golds,
silvers and bronzes out of the 5 people?





.



.