criteria applies to a range of dates.

[pebbles]

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)

 

[teamschneller]

        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 trainingeach
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.

 

[Herbert Seidenberg]

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

 

[pebbles]

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)}