Conditional Sum Formula

[johnnygirl51]

Hello -

I need help developing a formula for conditional sum - adding total years of service for employees.

So, where my columns are A ID, B Employer, C Start Date, D End Date, E Years at Job, I want to sum the years at job for each employee ID. Can anyone help me with a formula to do that?

Thanks!

[Gary Brown]

Try the following undocumented formula:-

=DATEDIF(C1,D1,"Y")

where C1 is the Start date cell, D1 is the End date cell and Y is the date diffrence in years

[johnnygirl51]

Hi Gary -

Thanks so much for responding. I'm not familiar with the DATEDIF formula. I could be missing something or misreading something, but I'm not sure that will get me where I need to be.

If my data looks like this:

ID Employer Start Date End Date Years

0000001 Employer B 1998-10-01 2000-07-01 1.8
0000001 Employer C 1998-05-01 1998-08-01 0.3
0000002 Employer D 2000-02-01 2001-03-15 1.1
0000002 Employer E 1999-06-01 2000-01-01 0.6
0000003 Employer F 1998-06-01 1998-08-31 0.2
0000003 Employer G 1996-12-01 1998-05-31 1.5
0000004 Employer H 1998-03-30 1999-12-29 1.8

I want to be able to write a formula that will add 1.8 and 0.3 for Employee 0000001 and then add 1.1 and 0.6 for Employee 0000002, so that I can get a total number of years of previous experience for each employee. I apologize if I'm missing something and appreciate your patience and help on this!!!!!!

[Gary Brown]

The formula is the same, except you replace the Y with an M for months divide this by 12, therfore the formula looks like the following:-

=DATEDIF(C1,D1,"M")/12

This will then give you as per your example:-

0000001 Employer B 1998-10-01 2000-07-01 1.75
0000001 Employer C 1998-05-01 1998-08-01 0.25
0000002 Employer D 2000-02-01 2001-03-15 1.08
0000002 Employer E 1999-06-01 2000-01-01 0.58
0000003 Employer F 1998-06-01 1998-08-31 0.17
0000003 Employer G 1996-12-01 1998-05-31 1.42
0000004 Employer H 1998-03-30 1999-12-29 1.67

Then you can either do a pivot table to get the totals for employee 0000001, 0000002 etc or you could have a table with the following formulas

A B
1 0000001 =sumif(employee ids - table above,A1,datedif calculation)
2 0000002 =sumif(employee ids - table above,A1,datedif calculation)
3 0000003 =sumif(employee ids - table above,A1,datedif calculation)
4 0000004 =sumif(employee ids - table above,A1,datedif calculation)

I have enclosed a smaple zipped up spreadsheet for you to have a look at