# Help with formula to return bottom five rows that are not zero

1. ## Help with formula to return bottom five rows that are not zero

Dear All

I was wondering whether someone can help me with a formula to achieve the following:

I have a spreadsheet (attached) which shows the dept and their sales. I need a formula to show me the bottom five departments if their sales is not zer. In the attached i need to return the worst department to be 2517 with 5 and then D392 with 5 , D308 with 15 and so on.

My actual spreadsheet has large number of rows and lots of the sales are 0 but I only need to show the bottom 5 departments whose sales were not zero.

Hope this makes sense and thanks in advance

2. ## Re: Help with formula to return bottom five rows that are not zero

=SMALL(IF(ISNA(MATCH(\$B\$2:\$B\$27,\$D\$1:D1,)),\$B\$2:\$B\$27),ROWS(\$A\$2:A2)) as array formula, d1=0

3. ## Re: Help with formula to return bottom five rows that are not zero

Dear tim201110

Can I please ask that whether I need to do the whole formula as an array or just upto ,d1=0 as it doesn't seem to work. Sorry if I am being naive.

What I need to do is in column d1:d5 list the bottom five departments which their sales wasn't zero and column e1:e5 show those department's sales.

Hope this makes sense.

4. ## Re: Help with formula to return bottom five rows that are not zero

ARRAY formulas are used
In D2, then drag down

=INDEX(\$A\$2:\$A\$27,SMALL(IF(\$B\$2:\$B\$27=\$E2,ROW(\$B\$2:\$B\$27),""),COUNTIF(\$B\$2:\$B\$27,\$E2)))

In E2 then drag down

=SMALL(IF(\$B\$2:\$B\$27>0,\$B\$2:\$B\$27,""),ROWS(\$E\$2:\$E2))

5. ## Re: Help with formula to return bottom five rows that are not zero

Dear kvsrinivasamurthy
Thanks for the quick response. I looked at the sheet and it shows Dep7 2715 as sales 0f 5 twice however 2517 and D392 have sales of 5. It seems the dept numbers and their sales is not pulled through correctly.

Any help is appreciate it

6. ## Re: Help with formula to return bottom five rows that are not zero

sorry for misunderstanding

7. ## Re: Help with formula to return bottom five rows that are not zero

Dear Tim201110

This worked perfectly

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1