Count number of employees at the end of each month

[pendle666]

Hello

I don't know whether or not this can be done with a formula or requires VB.

I have a spreadsheet with employees listed and the function they belong to. I need to count the number of workers in the Customer Services function as at the last day of each month.

The columsn are:

firstname, surname, worker_type, notes, function, start date, end date,

I currently have in my answer cell:

=COUNTIFS('Current'!$C$3:$C400,"Contractor",'Current'!$E$3:$E400,"CS")

In the 'current' worksheet there are also those who are going to be starting at a future date, so these need to be ignored. There are Contractors and PAYEs so I'm just counting contractors in CS and in this case who are employed still as at 31/10/13.


Can someone push me in the right direction?

regards

Denise

 

[SkipVought]

hi,

So you have a summary table that lists the last day of each month?

Then the LOGIC would be that the START must be prior to or equal to the date and the END must be after or equal to the date as and using NAMED RANGES based on your table headings and assuming that this summary table begins in A1 (Headings) and column A is your end of the month dates, then the formula in row 2 is...
[tt]
firstname, surname, worker_type, notes, function, start date, end date,
=sumproduct((worker_type="Contractor")*(function="CS")*(start_date<=A2)*(end_date>=A2))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Hi

This hasn't worked for me, the list of dates is in Q1-Q6 where the heading is called dates and then it's the last working day of each month from September 2013 to January 2014.

thanks anyway

 

[Andrzejek]

So that's not really a true statement here...
"The columsn are:"
[pre]
A B C D E F G
firstname surname worker_type notes function start_date end_date
...
[/pre]
because "the list of dates is in Q1-Q6"

It would be helpful if you would provide a sample of your data. Otherwise we can only guess what data you have and where you have it.

Have fun.

---- Andy

 

[SkipVought]

dates is in Q1-Q6

Huh? DATES? Totally a misnomer, at least as far as Excel date is concerned!

If your "dates" are quarters, which are usually defined as YEAR and Quarter within year, what does Q1 mean?

"You got lots of splainin to do!"

Need some Sample source data AND how that data would summarize in table in your world.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Hi

Sorry for the confusion - by Q1-Q6 - this was the cell reference that the dates were in, not quarters!

I have attached sample data - the blank columns are data that is not relevant to what I'm trying to achieve - there has also been a change in what's required since my original post.

So, the little table at the bottom are the results I need to achieve. I need to find :

all those contractors in area CS as at 31/10/13
all those PAYE in area CS as at 31/10/13
all those contractors in area CS who have returned their contracts as at 31/10/13
all those PAYE in area CS who have returned their contracts as at 31/10/13
all those contractors in area CS who have returned their handbooks as at 31/10/13
all those PAYE int area CS who have returned their handbooks as at 31/10/13

Because this spreadsheet has people added in when they've accepted an offer of employment, there will be those who have a start date into the next month and these need to be discounted, so it's not just a straightforward count up of people. In Cell B15 I have

=COUNTIFS('people'!C3:C400,"contractor",'people'!E3:E400,"CS") (count all those who are contractors in CS)

and in Cell C15

=COUNTIFS('people'!C3:C400,"contractor",'people'!E3:E400,"CS",'people'!AS3:AS400,"1") (count all those who are contractors in CS and there is a "1" in column AS

But it's getting the date in as well which is where I'm stuck.

Thank you for your help so far!!

 

[Andrzejek]

Due to safety and security, most of people at TT can not access your sample.

Please, provide the sample of your data here. you can format it like this [PREserve spacing]
[ignore][pre][/ignore][pre]
A B C D E F G
firstname surname worker_type notes function start_date end_date
...
[/pre]
[ignore][/pre][/ignore]

Have fun.

---- Andy

 

[pendle666]

Hi again,

Here is my data formatted as per Andy's suggestion.


[pre]
A B C E K AS AT AU
Forename Surname Type Area Start Date contract payroll handbook
Person Person Contractor CS 18-Nov-13 1 1 0
Person Person PAYE CS 22-Aug-12 1 1 1
Person Person PAYE Ops 01-Jul-13 1 0 1
Person Person Contractor Ops 24-Jan-11 0 0 1
Person Person Contractor CS 12-Oct-13 1 1 1
Person Person PAYE CS 01-Oct-13 0 1 1
Person Person PAYE CS 11-Nov-13 0 1 1
Person Person Contractor HR 13-Jun-12 1 1 0


Details as at 31/10/13 for CS
Headcount Contracts Handbooks
Contractor 1 1 1
PAYE 2 1 2

[/pre]

 

[SkipVought]

Where is END DATE?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

anyhow here's my MODIFIED formula without END DATE and WITH the heading changes in accordance with your posted example...
[tt]
=SUMPRODUCT((Type="Contractor")*(Area="CS")*(Start_Date<=A2))
[/tt]

and here are my results...
[pre]
eom headcount
8/31/2013 0
9/30/2013 0
10/31/2013 1
11/30/2013 2
12/31/2013 2
[/pre]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

End date isn't included because these are all people on contracts so they could be ending sometime next year - unless you think that would make it easier?

 

[SkipVought]

You don't design for only some conditions!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

Oh this was something I've inherited unfortunately.

I see that you've got

Start_Date<=A2

but where is A2 referenced? Because the A column is for the first name of the worker.

Sorry, being a thicky here.

 

[SkipVought]

14 Oct 13 10:37
hi,

So you have a summary table that lists the last day of each month?

Then the LOGIC would be that the START must be prior to or equal to the date and the END must be after or equal to the date as and using NAMED RANGES based on your table headings [highlight]and assuming that this summary table begins in A1 (Headings) and column A is your end of the month dates, then the formula in row 2 is...

firstname, surname, worker_type, notes, function, start date, end date,
=sumproduct((worker_type="Contractor")*(function="CS")*(start_date<=A2)*(end_date>=A2))[/highlight]

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[pendle666]

All sorted now - thank you very much for your help