Many thanks Duke,

Tried copying it down but there's some fixed references here so it looks
like I have to manually adjust all the formulae per cell!!! :-) Ho Hum such
is life lol

Terry

"Duke Carey" wrote:

Sorry, been a long day & forgot one important thing. This is an array
formula & needs to be entered by pressing Ctrl-Shift-Enter


"Duke Carey" wrote:

I'm sure there is a beeter formula for this & somebody willoffer it soon. In
the meantime, this works SO LONG AS your letter codes are all in caps

Put your codes in B1:B6, then put this formula in B1 & copy it down (adjust
the references to N1:N4 to match your data range)

=SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTI TUTE($N$1:$N$4,B2,"")*1)),SUBSTITUTE($N$1:$N$4,B2, "")*1))



"Tel" wrote:

Okay here's a challenge (well it was for me!)

I am creating a staff planner and need to count part-time staff hours.

the data that is entered has two variable
a7.5
a = type of absence;
T = training
S = sick
AL = Annual Leave
L = Lieu time
C = compassionate
CA = Carer's Leave

the number = the number of hours booked off

How do I seperate the two to make an additional column recognise the initial
letter and then total all occurences of that letter.

e.g. (Cell N11)= "AL5.5" + (Cell N15) = "AL2.5" would show in the Annual
Leave column as 8 hours leave (I would then reduce a further total column to
by the total)

If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls

Any suggestions to improve on this would be most welcome.

Tel