Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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..... -- chintu49 ------------------------------------------------------------------------ chintu49's Profile: http://www.excelforum.com/member.php...o&userid=18631 View this thread: http://www.excelforum.com/showthread...hreadid=374783 |
#2
|
|||
|
|||
Have a look at Aladin's contribution here... http://www.excelforum.com/showthread.php?t=333697 Hope this helps! -- Domenic ------------------------------------------------------------------------ Domenic's Profile: http://www.excelforum.com/member.php...o&userid=10785 View this thread: http://www.excelforum.com/showthread...hreadid=374783 |
#3
|
|||
|
|||
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 <atyahoo<dotcom ---- "chintu49" wrote in message ... 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 |
#4
|
|||
|
|||
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 | +-------------------------------------------------------------------+ -- olasa ------------------------------------------------------------------------ olasa's Profile: http://www.excelforum.com/member.php...o&userid=17760 View this thread: http://www.excelforum.com/showthread...hreadid=374783 |
#5
|
|||
|
|||
See the enclosed zip-file in ExcelTip forum:
http://www.excelforum.com/showthread...d=1#post985122 Hope it helped Ola Sandström |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
COUNTIF COMBINATION?? | Excel Worksheet Functions | |||
Combining IF and COUNTIF based on two columns | Excel Discussion (Misc queries) | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions | |||
Countif - Countif | Excel Worksheet Functions | |||
countif, again | Excel Worksheet Functions |