Counting based on location & dates
 

Thanks for reading this post.

Have an Excel sheet which includes among others the following column headings;

Employee No
Employee Name
Date of employment
Location
Salary amount

How could I count the number of employees who had joined the service between
two specific dates (eg. 01 Jan 2005 and 01 Jan 2006), for each location.

Counting based on location & dates
 

Just change those addresses to what you need.

=SUMPRODUCT(--(e2:e10="Leeds"),
--(g2:g10=date(2005,1,1),
--(g2:g10<=date(2006,5,31))

(And change the 10 to as much as you need--but not the entire column until
xl2007.)

You could put those dates in different cells, too.

My formula counted between Jan 1, 2005 and May 31,2005--only from Leeds.






jpreman wrote:

Thanks MIKE for your response,

Though they are not adjacent columns, the following is the sequence,

Employee name (Column B)
Location (Column E)
Date of employment (Column G)

Hope this info will be help you

Thanks



"Mike" wrote:

You don't say how your columns are ordered so I have assumed the date entered
service is column C and locations are column D. I have assumed a location of
Leeds but this could equally be a cell address.

=SUMPRODUCT(--(D2:D10="Leeds"),--(C2:C10G7),--(C2:C10<G8))

The 2 dates you specify are in cells G7 and G8

Mike

"jpreman" wrote:

Thanks for reading this post.

Have an Excel sheet which includes among others the following column headings;

Employee No
Employee Name
Date of employment
Location
Salary amount

How could I count the number of employees who had joined the service between
two specific dates (eg. 01 Jan 2005 and 01 Jan 2006), for each location.


--

Dave Peterson