countif max value help!!!

[chintu49]

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.....

 

[Domenic]

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

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

Hope this helps!

 

[Max]

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

 

[olasa]

I would use a Pivottable (see encl.)

Hope it helped
Ola Sandström


+-------------------------------------------------------------------+
|Filename: Book3.zip |
|Download: http://www.excelforum.com/attachment.php?postid=3436 |
+-------------------------------------------------------------------+

 

[Guest]

See the enclosed zip-file in ExcelTip forum:
http://www.excelforum.com/showthread.php?p=985122&posted=1#post985122

Hope it helped
Ola Sandström