Help with SUMPRODUCT between two dates

**steiom** · 04-03-2013, 04:15 PM

I have a sheet with HR info which has a 'start date' column and 'end date' for employees. On a summary page I want to show each month in the year, along with totals for each Type of employee (part time, full time etc) for that month.

Columns:
A - Name
B - Start Date
C - End Date
D - Type of Employee
E - Grade

The query for January (which i'm struggling with) needs to say: Check column D for type=x AND Start date is before Jan 1st, AND End date is NULL OR After 31st Jan. The aim is to show the true total of employees on the payroll that month.

Help please!

**steiom** · 04-03-2013, 04:23 PM

Here's what I have so far, which works apart from there's no check for an Empty 'end date' cell....:

=SUMPRODUCT(--(B9:B249<=DATE(2013,1,1))*(C9:C249>=DATE(2013,1,31))*(D9:D249=B3))

**Tony Valko** · 04-03-2013, 04:25 PM

What if the end date is something like Jan 15? Should that not be counted for the month of Jan?

Same thing for the start date. What if it's something like Jan 22? Should that not be counted for the month of Jan?

**steiom** · 04-03-2013, 04:27 PM

Not to fussed about that, happy for a count as at the end of the month, thanks for reply

**steiom** · 04-03-2013, 04:42 PM

If i put a default hyphen in the end date field indicating they are still in employment the above formula actually works. I have modified the 'start date to the end of the month too so it's an accurate picture.

**Tony Valko** · 04-03-2013, 04:44 PM

Ok, try this...

F2 = Jan 1 2013

Enter this formula in G2**:

=EOMONTH(F2,0)

Format as Date

H2 = X

=SUMPRODUCT(--(B2:B100<F2),SIGN((C2:C100>G2)+(C2:C100="")),--(D2:D100=H2))

Adjust the ranges to suit.

**
Note that the EOMONTH function requires the Analysis ToolPak
add-in be installed if you're using a version of Excel prior to
Excel 2007. If you enter the formula and get a #NAME?
error look in Excel help for the EOMONTH function. It'll tell you
how to fix the problem.