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

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

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