SUM IF with Date criteria

[eagle028]

Hi

I need help with setting up a headcount report for external contractors using excel (not using access as i don't know how to use it)

I've a worksheet with a namelist of Ees with a few columns like name, start date, end date, business group, location. Then i've another monthly headcount to show headcount at beginning of each month and then the movements like joiners and leavers in the month resulting in the headcount at end of each month.

I'm using the SUM IF formulae (array formula) to get the count the number of joiners and/or leavers by location and business line and contract type criteria (as there are several locations for each business line). Now i've a problem in incoporating the date criteria in the SUM IF formulae.

E.g If Start date is 20 March 2011 for an Ee and the headcount report is for March and i need to count this EE as a joiner if he belongs to business line of Sales and location in Japan.

My formula currently is {=SUM (IF('Business Line'="Sales", 1,0), IF('Location'="Japan",1,0))}

Please help me urgently.

Thanks a lot!

 

[Delta729]

Try this...


={SUM(IF(Business Line="Sales",IF(Location="Japan",IF(Start Date>="March 20, 2011,1),0),0))}

 

[eagle028]

Hi

Thanks for your reply however there are several joiners or leavers in the month of March 2011, and in the monthly headcount report, i'll indicate the month of the report e.g March 2011, so what would the formula? And i'll have 1 worksheet for each month e.g April 2011, May 2011 so i'll need to find those joiners & leavers with start dates in the month for March, April, May etc.

Cheers!

 

[Aladin Akyurek]

Hi

I need help with setting up a headcount report for external contractors using excel (not using access as i don't know how to use it)

I've a worksheet with a namelist of Ees with a few columns like name, start date, end date, business group, location. Then i've another monthly headcount to show headcount at beginning of each month and then the movements like joiners and leavers in the month resulting in the headcount at end of each month.

I'm using the SUM IF formulae (array formula) to get the count the number of joiners and/or leavers by location and business line and contract type criteria (as there are several locations for each business line). Now i've a problem in incoporating the date criteria in the SUM IF formulae.

E.g If Start date is 20 March 2011 for an Ee and the headcount report is for March and i need to count this EE as a joiner if he belongs to business line of Sales and location in Japan.

My formula currently is {=SUM (IF('Business Line'="Sales", 1,0), IF('Location'="Japan",1,0))}

Please help me urgently.

Thanks a lot!

I don't see the ranges involved... But I'll venture a guess:

Control+shift+enter, not just enter:

=SUM(IF(BusinessLineRange="Sales",IF(LoactionRange="Japan",IF(DateRange-DAY(DateRange)+1=DATE(2011,3,1),1))))

This gives you a count for the month/year pair of March/2011.