top ten filter

[Jake]

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

 

[Mike H]

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]

that works fine!
Thanks,
Not sure what the problem is with Excel but this is a fine work around.
Jake