countif max value help!!!

hi all,

well i am having small problem, i am having a sheet with Column A as name of the people and Column B as no of calls taken, what i want to do is that suppose kelly took 12 calls, john took 11 calls, matt took 10 calls, i want a formula which will only collect top five names who have taken maximum calls and should give information of those call takers and no of calls in different column. I will give you example once again

Column A Column B
John 11
Matt 10
Pat 2
Kelly 12
Keith 3
Judieth 4
July 9
Polly 5
Cat 4
Micheal 8
Peter 4


Now in a separate sheet I have two columns that is column A and Column B named Top 5 Names and top 5 counts, i want a formula which will look at the above data and give me the information the way it is given below. Is it possible ?

Column A Column B
Top 5 Names Top 5 Counts
Kelly 12
John 11
Matt 10
July 9
Micheal 8

Please help

Chintu.....

Have a look at Aladin's contribution here...

http://www.excelforum.com/showthread.php?t=333697

Hope this helps!

Just another play to try ..

Assume the source data is in Sheet1, cols A and B, from row1 down

Put in C1: =B1-ROW()/10^10
Copy down to C11
(Col C will act as an arbitrary tiebreaker for the # of calls in col B)

In say, Sheet2,
With the headers in A1:B1 : Top 5 Names, Top 5 Counts

Put in A2:
=INDEX(Sheet1!A:A,MATCH(LARGE(Sheet1!$C:$C,ROWS($A$1:A1)),Sheet1!$C:$C,0))
Copy across to B2, fill down to B6

A2:B6 will return the desired top 5 names and their # of calls
(assuming there are no ties in the # of calls for the top5 names)

You could, if you want, just copy A2:B2 down until #NUM! appears to exhaust
the entire list from Sheet1 in descending order by # of calls

In the event of any ties in the # of calls in col B of Sheet1, the names and
# of calls listed in Sheet2 will appear in the same relative order that they
are in Sheet1
For example, for the sample source data in Sheet1, copying A2:B2 down to B12
yields:

Kelly 12
John 11
Matt 10
July 9
Micheal 8
Polly 5
Judieth 4
Cat 4
Peter 4
Keith 3
Pat 2

where Judieth, Cat, Peter (all with 4 calls each) will appear in the
"relative" order above as this is the order that the names appear in Sheet1

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <at>yahoo<dot>com
----
"chintu49" <[email protected]> wrote in
message news:[email protected]...
>
> hi all,
>
> well i am having small problem, i am having a sheet with Column A as
> name of the people and Column B as no of calls taken, what i want to do
> is that suppose kelly took 12 calls, john took 11 calls, matt took 10
> calls, i want a formula which will only collect top five names who have
> taken maximum calls and should give information of those call takers and
> no of calls in different column. I will give you example once again
>
> Column A Column B
> John 11
> Matt 10
> Pat 2
> Kelly 12
> Keith 3
> Judieth 4
> July 9
> Polly 5
> Cat 4
> Micheal 8
> Peter 4
>
>
> Now in a separate sheet I have two columns that is column A and Column
> B named Top 5 Names and top 5 counts, i want a formula which will look
> at the above data and give me the information the way it is given
> below. Is it possible ?
>
> Column A Column B
> Top 5 Names Top 5 Counts
> Kelly 12
> John 11
> Matt 10
> July 9
> Micheal 8
>
> Please help
>
> Chintu.....
>
>
> --
> chintu49
> ------------------------------------------------------------------------
> chintu49's Profile:
http://www.excelforum.com/member.php...o&userid=18631
> View this thread: http://www.excelforum.com/showthread...hreadid=374783
>

I would use a Pivottable (see encl.)

Hope it helped
Ola Sandström

See the enclosed zip-file in ExcelTip forum:
http://www.excelforum.com/showthread...d=1#post985122

Hope it helped
Ola Sandström