One way is to use SUMIF to sum the "Totals" col E by the names in col B, then
check the result vs the criteria of 20 days = Long Term
Put this in F2:
=IF(SUMIF(B:B,B2,E:E)20,"Long Term",IF(SUMIF(B:B,B2,E:E)0,"Short Term",""))
Copy down. Success? celebrate it, hit YES below
--
Max
Singapore
---
"Charles S" wrote:
I have a spreadsheet which shows staff sickness each month. I would like to
classify whether each absence is 'short term' or 'long term' in column F.
Any absence which is continuous 20 days or more would be classed as long term.

Here's an example

A B C D
E F
Ref No Name Start Date End Date Total
Length
3421 Jones 21/11/09 30/11/09 10
Long Term
3421 Jones 01/12/09 17/12/09 17
Long Term
8756 Sue 02/07/09 08/07/09 7
Short Term


In this example, Mr Jones has had 27 days continous absence in total and so
this would be a long term absence. The text in column F is what I want to
see as a result.

Any help would be really useful.