SORT formula, exclude zeros

[virtuosok]

Hi,
I have the following formula which works reasonably well for my purposes (column B contains names and column BL contains numbers):

=SORT(FILTER(CHOOSE({1,2},IMP!$B$2:$B$301,IMP!$BL$2:$BL$301),IMP!BL$2:$BL$301>=LARGE(IMP!$BL$2:$BL$301,10)),2,-1)

It returns data in the following format:

John	11
Jack	8
Jillian	7
Joana	5
Jakub	0
Johannes	0
Jedda	0

...and then dozens of additional rows with names and zeros, this spills way too much and doesn't provide any added value.

Is it possible to update the formula so that it either
- excludes zeros (i.e. in the example above it would only return 4 initial entries) OR
- limits the # of entries to 10 (so that in the example above it returns 4 meaningful entries followed by 6 entries with zeros)?
THANKS...

 

[RoryA]

You can just add in another criterion to the Filter like this:

=SORT(FILTER(CHOOSE({1,2},IMP!$B$2:$B$301,IMP!$BL$2:$BL$301),(IMP!BL$2:$BL$301>=LARGE(IMP!$BL$2:$BL$301,10))*(IMP!BL$2:$BL$301>0)),2,-1)

or if you only have whole numbers to deal with, you could use:

=SORT(FILTER(CHOOSE({1,2},IMP!$B$2:$B$301,IMP!$BL$2:$BL$301),IMP!BL$2:$BL$301>=MAX(1,LARGE(IMP!$BL$2:$BL$301,10))),2,-1)

 

[virtuosok]

Great, this works! Thanks a lot!!!