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

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

Sorry, that was supposed to be "put the formula in C1 & copy down"

"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

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

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

Tel Wrote:
Any suggestions to improve on this would be most welcome.


I couldn't view your spreadsheet, I got an error but I'd suggest that
it would be much simpler if you put the codes and associated hours in
two adjacent cells - then you could use some less complex and more
easily maintained SUMIF formulas, e.g

SUMIF(A$1:A$10,"AL",B$1:B$10)


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=525858

I tried to look at your file but I got an error 404: file not found.

Biff

"Tel" wrote in message
...
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

With your data in A2:A11 and the type of absence in B2

=SUMPRODUCT(--(LEFT(SUBSTITUTE($A$2:$A$11,"CA","^^"),LEN(B2))=IF (B2="CA","^^",B2)),--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(0&SUBSTITUTE($A$2:$A$11,"T","")," S",""),"AL",""),"CA","^^"),"C",""),"L",""),"^^","" )))

having said that you should really put the absence in one cell and the hours
on another, assume the type was in A2:A11, the hours in B2:B11 and the
criteria in C2

=SUMIF($A$2:$A$11,C2,$B$2:$B$11)

you could easily extract the numbers from the range by using

=--MID(A2,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A2&"012345 6789")),1024)

and copy down






--

Regards,

Peo Sjoblom



"Tel" wrote in message
...
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