Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF
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) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF
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(D2:D5,"<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. "pebbles" wrote: 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) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF
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" wrote in message ... 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) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMIF
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) "Bob Phillips" wrote: 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" wrote in message ... 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
sumif for multi conditions. i.e sumif(A1:A10,"Jon" and B1:B10,"A" | Excel Worksheet Functions | |||
sumif | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
nested sumif or sumif with two criteria | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |