conditional summation

Hi

I'm new here please be patient! i'm trying to organise a spreadsheet to log staff sicknesses and flag up whether they're a "cause for concern" or not. There are 4 criteria for a cause for concern. I've done the formulas for three of them fine, all in seperate columns, but I'm really struggling with the last! What I need to flag is whether the staff member has had 10 days off or more in total within 12 months. The dates are not set, it's a rolling 12 months back from the date of the latest sickness.

Each worksheet is for a seperate staff member all within one workbook and I have a summary sheet on the front. Within each worksheet I've got columns to enter the start and end date of each sickness, and a column to show working days off sick. You enter each new sickness date on a new row and the rest works itself out.

I need to put in a new column a formula which says that when you add a new sickness, to count back one year from the start date and add up the total days off from all rows whereby the date column falls into the 12 month date range. I want it to show True if the total is more than 9.

Is it possible? Does that even make sense?

Would really appreciate some help!

Thanks

No, unfortunately I work for a council and they block uploading as well as downloading for some reason.

I'll try and show the example here simply - not sure if it will work! (There are several other columns but they'll confuse things!):

A---------------B----------------C-----------------------D
Start Date--|--End date--|--total days off--|--10 days off within 12 months?

10/10/09---|--12/10/09--|--------1--------|----------FALSE
15/10/09---|--21/10/09--|--------5--------|----------FALSE
03/11/09---|--06/11/09--|--------4--------|----------TRUE



for column D, I want to add up the totals in Column C from the last row back, but only for the rows whereby the dates in column A fall within 12 months from the last row. Then if it's 10 or more, to show True

And so what happens if the 12 month period should start mid absence on an old record ?

The total days off calculation is seemingly workday related (ie NETWORKDAYS or somesuch) but I would be curious as to why (if weekends are excluded) a start/end date should ever fall on a weekend date in the first instance ?