| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
top ten filter
Hello,
I would like to show the top ten sales values for a year. There are about 60 sales values for 2003 in the database, as well as values for other years. When I use auto filter and filter for the year 2003 I get only 2003 records. That's fine. But when I use the top ten filter on the Sales values I get only five records, not the top ten for the year 2003, as expected. I think Excel is using all the records, including other years and only displaying 2003. Is there a way to get the top 10 2003 records? Thanks for any help. Jake |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
top ten filter
Hi,
A different approach. Lets assume your data are in columns A&B starting in A2. Put this in c2 and drag down. =SUMPRODUCT(--($A$2:$A$20=A2),--(B2<$B$2:$B$20))+1 Filter column A by 2003 and then column C by <=10 Mike "Jake" wrote: Hello, I would like to show the top ten sales values for a year. There are about 60 sales values for 2003 in the database, as well as values for other years. When I use auto filter and filter for the year 2003 I get only 2003 records. That's fine. But when I use the top ten filter on the Sales values I get only five records, not the top ten for the year 2003, as expected. I think Excel is using all the records, including other years and only displaying 2003. Is there a way to get the top 10 2003 records? Thanks for any help. Jake |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
top ten filter
that works fine!
Thanks, Not sure what the problem is with Excel but this is a fine work around. Jake "Mike H" wrote: Hi, A different approach. Lets assume your data are in columns A&B starting in A2. Put this in c2 and drag down. =SUMPRODUCT(--($A$2:$A$20=A2),--(B2<$B$2:$B$20))+1 Filter column A by 2003 and then column C by <=10 Mike "Jake" wrote: Hello, I would like to show the top ten sales values for a year. There are about 60 sales values for 2003 in the database, as well as values for other years. When I use auto filter and filter for the year 2003 I get only 2003 records. That's fine. But when I use the top ten filter on the Sales values I get only five records, not the top ten for the year 2003, as expected. I think Excel is using all the records, including other years and only displaying 2003. Is there a way to get the top 10 2003 records? Thanks for any help. Jake |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| DataFilterAuto Filter in excel 2007? | New Users to Excel | |||
| Filter PivotTable dropdown items to match report filter | Excel Discussion (Misc queries) | |||
| Copy only visible cells after filter is applied/ sum after filter | Excel Worksheet Functions | |||
| Excel auto filter doesn't recoginize case - won't filter AA from A | Excel Discussion (Misc queries) | |||
| "Criteria Range" in the "Data/Filter/Advanced Filter" to select Du | Excel Worksheet Functions | |||
Linear Mode
