Calculating number of employees by month
Posted by Sasa on January 22, 2002 6:48 AM
Dear all,
I have 1200 employees working for our company in deferent periods of time. I have sheet with all their names, start and end date of the contract. I would like to calculate how many persons we have working for us by month. Of course formula has to divide number of employes by month/year. (Something like Jan/2001=50, Feb/2001=54 ...) I hope you understood my question.
Many thanks in advance for consideration in this matter.
Best regards
Sasa
Posted by Russell Hauf on January 22, 2002 7:37 AM
Let's say that the start dates are in cells B2:B28 and the end dates are in cells C2:C28 (I know you have more, but this is just an example). Either on the same sheet or on another sheet, fill in 13 rows with dates that are all the first of the month. For example, I want to know the number of employees for each month of 2001, so I have the following in my column:
01/01/01
02/01/01
03/01/01
04/01/01
05/01/01
06/01/01
07/01/01
08/01/01
09/01/01
10/01/01
11/01/01
12/01/01
01/01/02
(You need one more month than what you are calculating for).
Say those dates are in cells K2:K14. In L2 type the following formula:
=SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*$D$2:$D$28)
Replace your actual ranges/cells and then pull the formula down to L13.
Hope this helps,
Russell
Posted by Russell Hauf on January 22, 2002 7:38 AM
Correction...
Change that formula to:
=SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*1)
Sorry about that,
-rh
Posted by Russell Hauf on January 22, 2002 7:40 AM
Arrgh - my clipboard is malfunctioning!
Try this:
=SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*1) Change that formula to: =SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*1) Sorry about that, -rh : Let's say that the start dates are in cells B2:B28 and the end dates are in cells C2:C28 (I know you have more, but this is just an example). Either on the same sheet or on another sheet, fill in 13 rows with dates that are all the first of the month. For example, I want to know the number of employees for each month of 2001, so I have the following in my column
Posted by Russell Hauf on January 22, 2002 7:41 AM
Re: Arrgh - my clipboard is malfunctioning!
This must be some kind of HTML thing - I'll try again:
Try this: =SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*1) : Change that formula to
=SUMPRODUCT(($B$2:$B$28<K3)*($C$2:$C$28>=K2)*1)
Posted by Russell Hauf on January 22, 2002 7:44 AM
Re: Arrgh - my clipboard is malfunctioning!
Well, for some reason the whole formula is not showing up. Here is my last try (on several lines - change yours to one line of course):
=SUMPRODUCT(
($B$2:$B$28<K3)*
($C$2:$C$28>=K2)*1)
for some reason this board is cutting my formula short!
A thousand apologies,
Russell This must be some kind of HTML thing - I'll try again:
Posted by Juan Pablo G. on January 22, 2002 7:48 AM
Re: Arrgh - my clipboard is malfunctioning!
How about this
=SUMPRODUCT(($B$2:$B$28 < K3)*($C$2:$C$28 >= K2)*1)
Juan Pablo G.
Posted by Russell Hauf on January 22, 2002 7:55 AM
That's the one!
That's the one! Thanks Juan Pablo. I guess I needed a space between my < and my K3, eh?
-rh How about this =SUMPRODUCT(($B$2:$B$28 < K3)*($C$2:$C$28 >= K2)*1) Juan Pablo G.
