Formulae to count employees monthly

[eagle028]

Hi

I've a workbook with 2 sheets in it: in 1 there's information such as below
- employee name, country, contract type, date join and date left e.g.

Last Name First Name Start Date End Date Ctry Contract Type
Li ABC 01-Feb-11 31-Dec-11 China Full Time
Shan XYZ 01-Mar-11 31-May-11 China Part Time
Doe Joe 01-Jun-11 30-Jun-11 Australia Contract
Chandra Raj 05-Jul-11 31-Dec-12 India Full Time

in the other spreadsheet, in the 1st column there's the month of headcount and the countries in the columns and different contract type on the rows e.g.
June-11 Australia China India Japan
Full Time
Part Time
Contract
Total

So what i need is a formulae that will help me count the number of employees (that are still active) in June 11 in each country for each contract type.

Also, if i want to use "Name" for the columns under the 1st spreadsheet, how do i do that? Do i name the whole column or just the header of the column?

Please HELP!

Thanks!

 

[eagle028]

Hi

Anybody can help me urgently on this? I really need to complete this work so i need the result to show as that in June 11:

China has 1 Full Time
Australia has 1 Contract

Then in July
China & India has 1 Full Time
Australia has 1 Contract

and so on & so forth for the coming months.

Sorry i don't seem to be able to copy a nice table into this post to show the e.g

 

[Akashwani]

Hi,

Is this how your data is set up?...

Excel Workbook
	A	B	C	D	E	F
1	Last Name	First Name	Start Date	End Date	Ctry	Contract Type
2	Li	ABC	01/02/2011	31/12/2011	China	Full Time
3	Shan	XYZ	01/03/2011	31/05/2011	China	Part Time
4	Doe	Joe	01/06/2011	30/06/2011	Australia	Contract
5	Chandra	Raj	05/07/2011	31/12/2012	India	Full Time
Employees

Is this the result you are expecting for June?.....

Excel Workbook
	A	B	C	D	E
1	June 11	Australia	China	India	Japan
2	Full Time	0	1	1	0
3	Part Time	0	0	0	0
4	Contract	1	0	0	0
5	Total	1	1	1	0
6	*	*	*	*	*
7	For Excel 2007-2010	*	*	*	*
8	June 11	Australia	China	India	Japan
9	Full Time	0	1	1	0
10	Part Time	0	0	0	0
11	Contract	1	0	0	0
12	Total	1	1	1	0
Results

The formulas will need to be copied across and down.
I hope that helps.

Ak

 

[eagle028]

Hi AK

Thanks a lot for your reply.

However for the month of Jun 11, results should be
China ----> 1 Full time
Australia -> 1 Contract

There is no count for India as full time employee only starts in July 11.

Then in July 11, results should be
China --> 1 Full time
India ---> 1 Full time

There is no count for Australia as employee's last day was 30 Jun 11

I'm using SUMIF & have gotten it so far as: {=SUM (IF('Ctry'="China", 1,0), IF('Contract Type'="Full Time",1,0))}

I just can't figure out the date range part

Please help advise

Thanks a lot!

 

[Akashwani]

Hi,

Does this work for you?..

Excel Workbook
	A	B	C	D	E
1	June 11	Australia	China	India	Japan
2	Full Time	0	1	0	0
3	Part Time	0	0	0	0
4	Contract	1	0	0	0
5	Total	1	1	0	0
Results

Excel Workbook
	A	B	C	D	E
1	July 11	Australia	China	India	Japan
2	Full Time	0	1	1	0
3	Part Time	0	0	0	0
4	Contract	0	0	0	0
5	Total	0	1	1	0
Results

The date in A1 has been entered as the last day of the month 30/06/11 of 31/06/11 and formatted as mmmm yy

Ak

 

[eagle028]

Hi AK

I tried this but somehow i get "N/A" on some cells for July 2011 and it seems to be the date part of the fomulae. I can't paste my excel to show you here.

My formulae is =SUMPRODUCT(($F10:$F13=D$21)*($G10:$G13=C$24)*($E$9:$E$13>=$B$21)*($D$9:$D$13<=$B$21))

Where $F$10:F$13 is Ctry & D$21 is Australia; $G10:$G13 is Contract Type & C$24 is Contract; $E$9:$E$13 is End date &$B$21 is July 2011; $D$9:$D$13 is Start date.

Would you be able to advise?

Thanks so much!

 

[Akashwani]

Is this what you have?.....

Excel Workbook
	B	C	D	E	F	G
9	Last Name	First Name	Start Date	End Date	Ctry	Contract Type
10	Li	ABC	01/02/2011	31/12/2011	China	Full Time
11	Shan	XYZ	01/03/2011	31/05/2011	China	Part Time
12	Doe	Joe	01/06/2011	30/06/2011	Australia	Contract
13	Chandra	Raj	05/07/2011	31/12/2012	India	Full Time
Employees

I get this ....

Excel Workbook
	C	D	E	F	G
21	July 11	Australia	China	India	Japan
22	Full Time	0	1	1	0
23	Part Time	0	0	0	0
24	Contract	0	0	0	0
25	Total	0	1	1	0
Results

All I can suggest is that you make sure that all your ranges are the same within the sumproduct formula, you cannot have something like this....

=SUMPRODUCT(($F10:$F13=D$21)*($G10:$G13=C$24)*($E$9:$E$13>=$B$21)*($D$9:$D$13<=$B$21))

I hope that solves it for you.

Ak