SUMIF

[pebbles]

Employee: Mary
Hire Date: 4/22/2006
A B C D E
Training Date Current Hours: By Next Anniv.

4/16/08 7.5 4/22/2009
2/1/08 3
6/10/07 7.5
5/12/06 3

Total


How can I SUM column C, only IF the dates in column A are current?
Trainings are good for one year, from one anniversary of hire date to the
next. (5/12/06 would not count for this years training.) This person should
have 18 hours of current training. I don't want to delete the out of date
training though. I tried SUMIF but it keeps giving me 0. This is how I
tried it. (Don't laugh, I'm learning.)

=SUMIF(A4:A11,"($E$4)-365>",C4:C11)

 

[Curtis]

I would create another column (Column D in my sheet) that calculates the
difference between today and the Training Date (=TODAY()-A2)

Then your sumif becomes:
=SUMIF(D25,"<365",B2:B5)

Where Column D is the new formula, and column B is the Current Hours. This
gives me a total of 18 hours.

 

[Bob Phillips]

Can you explain why the other 3 are included? As all dates are less than
16th April 2008, aren't they all done?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[pebbles]

The employee needs to complete 15 hours of ongoing training each year from
anniversary to anniversary. I don't want to delete the previous trainings
but I don't want them to show up in the current year total. Column A is the
training date, B is for the training topic, C is the number of hours the
training is worth, D is the date 15 training hours must be completed by.
(Next anniversary date). Some of the trainings are not within the current
year of employment or will expire when the employee has an anniversary. I
want to sum the hours in column C if the trainings fall within the date
range. (4/22/08 - 4/22/09)