A B C D
Employee: Mary
Hire Date: 4/22/2006
Training Topic Hours Next Anniv.
4/16/08 Fish 7.5 4/22/2009
2/1/08 Birds 3
6/10/07 Cats 7.5
5/12/06 Dogs 3
____
Total

How can I SUM column C, only IF the dates in column A fall within a certain
date range? The employee needs to complete 15 hours of ongoing training each
year from anniversary to anniversary. (5/12/06 would not count for this
years total because it started over on 4/22/07.) This person should have 18
hours of current training. I don't want to delete the previous trainings but
I don't want them to show up in the current year total. I tried SUMIF but it
keeps giving me €œ0€�.

I want to sum the hours in column C if the training dates in column a fall
within the calculated date range. (4/22/08 - 4/22/09)

On May 5, 4:43*pm, pebbles wrote:
* * * * A * * * * * * * * B * * * * * C * * * * * * *D * * * * *
* * * * Employee: Mary
* * * * Hire Date: 4/22/2006
* * * * Training * * Topic * * *Hours * *Next Anniv.
* * * * 4/16/08 * * *Fish * * * 7.5 * * * * * * 4/22/2009
* * * * 2/1/08 *Birds * 3
* * * * 6/10/07 * * * * Cats * *7.5
* * * * 5/12/06 * * *Dogs * * * 3
* * * * * * * * * * * * * * * * ____
* * * * * * * * * * * * * * * * Total

How can I SUM column C, only IF the dates in column A fall within a certain
date range? *The employee needs to complete 15 hours of ongoing training each
year from anniversary to anniversary. *(5/12/06 would not count for this
years total because it started over on 4/22/07.) *This person should have 18
hours of current training. *I don't want to delete the previous trainings but
I don't want them to show up in the current year total. *I tried SUMIF but it
keeps giving me “0”. *

I want to sum the hours in column C if the training dates in column a fall
within the calculated date range. *(4/22/08 - 4/22/09)

You can create another column which takes Anniversary date - Training
Date. This will give you the number of days between the two dates,
then you can use the Sumif function. =sumif(columns where data
Annivesary Date- training date is held, "<366",column where training
hours is held)
i.e. =SUMIF(E31:E34,"<366",C31:C34)


There may be a way to do the comparison in the sumif statement itself,
but I don't know.

" wrote:

On May 5, 4:43 pm, pebbles wrote:
A B C D
Employee: Mary
Hire Date: 4/22/2006
Training Topic Hours Next Anniv.
4/16/08 Fish 7.5 4/22/2009
2/1/08 Birds 3
6/10/07 Cats 7.5
5/12/06 Dogs 3
____
Total

How can I SUM column C, only IF the dates in column A fall within a certain
date range? The employee needs to complete 15 hours of ongoing training each
year from anniversary to anniversary. (5/12/06 would not count for this
years total because it started over on 4/22/07.) This person should have 18
hours of current training. I don't want to delete the previous trainings but
I don't want them to show up in the current year total. I tried SUMIF but it
keeps giving me €œ0€�.

I want to sum the hours in column C if the training dates in column a fall
within the calculated date range. (4/22/08 - 4/22/09)

You can create another column which takes Anniversary date - Training
Date. This will give you the number of days between the two dates,
then you can use the Sumif function. =sumif(columns where data
Annivesary Date- training date is held, "<366",column where training
hours is held)
i.e. =SUMIF(E31:E34,"<366",C31:C34)


There may be a way to do the comparison in the sumif statement itself,
but I don't know.

I took your suggestion, played with it a little and came up with this which
worked perfectly. Thanks for your help!
{SUM(IF($A$20<=(A5:A19),(C5:C19)*1),(C5:C19)*0)}

Use Pivot Table for a
no formula solution:
http://www.freefilehosting.net/download/3gk7h