# Formula to find inactive employees

1. ## Formula to find inactive employees

Hello:

I have monthly payroll info for lots of employees and some are in the attached sheet.
I need to figure out the current inactive employees.
i would select the start month and end month from drop down list in cell A17 and B17.
So lets say i have selected A17 as Sep-14 and B17 as Nov-14.
I need a formula to list in cell A20 download so that if any employee listed in row 1 between these months
have zero payroll will be treated as inactive.
I have manually entered inactive employee as shown in cell A19 downwards.

Please let me know if you have any questions.
Thanks.

Riz

2. ## Re: Formula to find inactive employees

Use a helper row..

See attached.. formulas highlighted in green

3. ## Re: Formula to find inactive employees

Hi Ace_XL:

Very smart, thanks a lot for great work.
Merry XMas

Riz

4. ## Re: Formula to find inactive employees

In A19 and copy down

=IFERROR(INDEX(\$B\$1:\$H\$1,SMALL(IF((Month>\$A\$17)*(Month<\$B\$17)*(\$B\$2:\$H\$13=0),COLUMN(\$B\$2:\$H\$13)-COLUMN(B2)+1),ROWS(A\$1:A1))),"")

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

5. ## Re: Formula to find inactive employees

Hi Alkey:

Thanks a lot.
Works great
Riz

6. ## Re: Formula to find inactive employees

Hello Alkey:

I am trying to use the formula in my situation and i am liitle lost.
Please refer to attahched sheet and go to sheet2.
The data is shown and need the above formula in cell L35.
Thanks a lot.
Riz

7. ## Re: Formula to find inactive employees

Hi Riz,

Please see revised formula on Sheet2. Please note that I changed the named range on Sheet2 to Month2

8. ## Re: Formula to find inactive employees

Hello Alkey:

I have extended the data and I have change the formula in cell L35 to reflect the increase in data but for some reason the formula does not give correct result.
Let me know if you have any questions.
Thanks.

9. ## Re: Formula to find inactive employees

Formula didn't work because you did not adjust ranges.

10. ## Re: Formula to find inactive employees

Hi Alkey:

Thanks a lot...
I see it, works great.
Thanks once again.
Riz

11. ## Re: Formula to find inactive employees

You'r welcome and thank you for the feedback!

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