software

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)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; 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.

TelI'm sure there is a beeter formula for this amp; 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 amp; 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,quot;quot;)*1)),SUBSTITUTE($N$1:$N$4,B2, quot;quot;)*1))
quot;Telquot; wrote:

gt; Okay here's a challenge (well it was for me!)
gt;
gt; I am creating a staff planner and need to count part-time staff hours.
gt;
gt; the data that is entered has two variable
gt; a7.5
gt; a = type of absence;
gt; T = training
gt; S = sick
gt; AL = Annual Leave
gt; L = Lieu time
gt; C = compassionate
gt; CA = Carer's Leave
gt;
gt; the number = the number of hours booked off
gt;
gt; How do I seperate the two to make an additional column recognise the initial
gt; letter and then total all occurences of that letter.
gt;
gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the Annual
gt; Leave column as 8 hours leave (I would then reduce a further total column to
gt; by the total)
gt;
gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt;
gt; Any suggestions to improve on this would be most welcome.
gt;
gt; Tel
gt;

Sorry, that was supposed to be quot;put the formula in C1 amp; copy downquot;

quot;Duke Careyquot; wrote:

gt; I'm sure there is a beeter formula for this amp; somebody willoffer it soon. In
gt; the meantime, this works SO LONG AS your letter codes are all in caps
gt;
gt; Put your codes in B1:B6, then put this formula in B1 amp; copy it down (adjust
gt; the references to N1:N4 to match your data range)
gt;
gt; =SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTI TUTE($N$1:$N$4,B2,quot;quot;)*1)),SUBSTITUTE($N$1:$N$4,B2, quot;quot;)*1))
gt;
gt;
gt;
gt; quot;Telquot; wrote:
gt;
gt; gt; Okay here's a challenge (well it was for me!)
gt; gt;
gt; gt; I am creating a staff planner and need to count part-time staff hours.
gt; gt;
gt; gt; the data that is entered has two variable
gt; gt; a7.5
gt; gt; a = type of absence;
gt; gt; T = training
gt; gt; S = sick
gt; gt; AL = Annual Leave
gt; gt; L = Lieu time
gt; gt; C = compassionate
gt; gt; CA = Carer's Leave
gt; gt;
gt; gt; the number = the number of hours booked off
gt; gt;
gt; gt; How do I seperate the two to make an additional column recognise the initial
gt; gt; letter and then total all occurences of that letter.
gt; gt;
gt; gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the Annual
gt; gt; Leave column as 8 hours leave (I would then reduce a further total column to
gt; gt; by the total)
gt; gt;
gt; gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt; gt;
gt; gt; Any suggestions to improve on this would be most welcome.
gt; gt;
gt; gt; Tel
gt; gt;

Sorry, been a long day amp; forgot one important thing. This is an array
formula amp; needs to be entered by pressing Ctrl-Shift-Enterquot;Duke Careyquot; wrote:

gt; I'm sure there is a beeter formula for this amp; somebody willoffer it soon. In
gt; the meantime, this works SO LONG AS your letter codes are all in caps
gt;
gt; Put your codes in B1:B6, then put this formula in B1 amp; copy it down (adjust
gt; the references to N1:N4 to match your data range)
gt;
gt; =SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTI TUTE($N$1:$N$4,B2,quot;quot;)*1)),SUBSTITUTE($N$1:$N$4,B2, quot;quot;)*1))
gt;
gt;
gt;
gt; quot;Telquot; wrote:
gt;
gt; gt; Okay here's a challenge (well it was for me!)
gt; gt;
gt; gt; I am creating a staff planner and need to count part-time staff hours.
gt; gt;
gt; gt; the data that is entered has two variable
gt; gt; a7.5
gt; gt; a = type of absence;
gt; gt; T = training
gt; gt; S = sick
gt; gt; AL = Annual Leave
gt; gt; L = Lieu time
gt; gt; C = compassionate
gt; gt; CA = Carer's Leave
gt; gt;
gt; gt; the number = the number of hours booked off
gt; gt;
gt; gt; How do I seperate the two to make an additional column recognise the initial
gt; gt; letter and then total all occurences of that letter.
gt; gt;
gt; gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the Annual
gt; gt; Leave column as 8 hours leave (I would then reduce a further total column to
gt; gt; by the total)
gt; gt;
gt; gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt; gt;
gt; gt; Any suggestions to improve on this would be most welcome.
gt; gt;
gt; gt; Tel
gt; gt;

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

quot;Duke Careyquot; wrote:

gt; Sorry, been a long day amp; forgot one important thing. This is an array
gt; formula amp; needs to be entered by pressing Ctrl-Shift-Enter
gt;
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt; gt; I'm sure there is a beeter formula for this amp; somebody willoffer it soon. In
gt; gt; the meantime, this works SO LONG AS your letter codes are all in caps
gt; gt;
gt; gt; Put your codes in B1:B6, then put this formula in B1 amp; copy it down (adjust
gt; gt; the references to N1:N4 to match your data range)
gt; gt;
gt; gt; =SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTI TUTE($N$1:$N$4,B2,quot;quot;)*1)),SUBSTITUTE($N$1:$N$4,B2, quot;quot;)*1))
gt; gt;
gt; gt;
gt; gt;
gt; gt; quot;Telquot; wrote:
gt; gt;
gt; gt; gt; Okay here's a challenge (well it was for me!)
gt; gt; gt;
gt; gt; gt; I am creating a staff planner and need to count part-time staff hours.
gt; gt; gt;
gt; gt; gt; the data that is entered has two variable
gt; gt; gt; a7.5
gt; gt; gt; a = type of absence;
gt; gt; gt; T = training
gt; gt; gt; S = sick
gt; gt; gt; AL = Annual Leave
gt; gt; gt; L = Lieu time
gt; gt; gt; C = compassionate
gt; gt; gt; CA = Carer's Leave
gt; gt; gt;
gt; gt; gt; the number = the number of hours booked off
gt; gt; gt;
gt; gt; gt; How do I seperate the two to make an additional column recognise the initial
gt; gt; gt; letter and then total all occurences of that letter.
gt; gt; gt;
gt; gt; gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the Annual
gt; gt; gt; Leave column as 8 hours leave (I would then reduce a further total column to
gt; gt; gt; by the total)
gt; gt; gt;
gt; gt; gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt; gt; gt;
gt; gt; gt; Any suggestions to improve on this would be most welcome.
gt; gt; gt;
gt; gt; gt; Tel
gt; gt; gt;


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

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,quot;ALquot;,B$1:B$10)--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: www.excelforum.com/member.php...oamp;userid=30486
View this thread: www.excelforum.com/showthread...hreadid=525858I tried to look at your file but I got an error 404: file not found.

Biff

quot;Telquot; gt; wrote in message
...
gt; Many thanks Duke,
gt;
gt; Tried copying it down but there's some fixed references here so it looks
gt; like I have to manually adjust all the formulae per cell!!! :-) Ho Hum
gt; such
gt; is life lol
gt;
gt; Terry
gt;
gt; quot;Duke Careyquot; wrote:
gt;
gt;gt; Sorry, been a long day amp; forgot one important thing. This is an array
gt;gt; formula amp; needs to be entered by pressing Ctrl-Shift-Enter
gt;gt;
gt;gt;
gt;gt; quot;Duke Careyquot; wrote:
gt;gt;
gt;gt; gt; I'm sure there is a beeter formula for this amp; somebody willoffer it
gt;gt; gt; soon. In
gt;gt; gt; the meantime, this works SO LONG AS your letter codes are all in caps
gt;gt; gt;
gt;gt; gt; Put your codes in B1:B6, then put this formula in B1 amp; copy it down
gt;gt; gt; (adjust
gt;gt; gt; the references to N1:N4 to match your data range)
gt;gt; gt;
gt;gt; gt; =SUMPRODUCT(--(LEFT($N$1:$N$4,LEN(B2))=B2),IF(NOT(ISERROR(SUBSTI TUTE($N$1:$N$4,B2,quot;quot;)*1)),SUBSTITUTE($N$1:$N$4,B2, quot;quot;)*1))
gt;gt; gt;
gt;gt; gt;
gt;gt; gt;
gt;gt; gt; quot;Telquot; wrote:
gt;gt; gt;
gt;gt; gt; gt; Okay here's a challenge (well it was for me!)
gt;gt; gt; gt;
gt;gt; gt; gt; I am creating a staff planner and need to count part-time staff
gt;gt; gt; gt; hours.
gt;gt; gt; gt;
gt;gt; gt; gt; the data that is entered has two variable
gt;gt; gt; gt; a7.5
gt;gt; gt; gt; a = type of absence;
gt;gt; gt; gt; T = training
gt;gt; gt; gt; S = sick
gt;gt; gt; gt; AL = Annual Leave
gt;gt; gt; gt; L = Lieu time
gt;gt; gt; gt; C = compassionate
gt;gt; gt; gt; CA = Carer's Leave
gt;gt; gt; gt;
gt;gt; gt; gt; the number = the number of hours booked off
gt;gt; gt; gt;
gt;gt; gt; gt; How do I seperate the two to make an additional column recognise the
gt;gt; gt; gt; initial
gt;gt; gt; gt; letter and then total all occurences of that letter.
gt;gt; gt; gt;
gt;gt; gt; gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the
gt;gt; gt; gt; Annual
gt;gt; gt; gt; Leave column as 8 hours leave (I would then reduce a further total
gt;gt; gt; gt; column to
gt;gt; gt; gt; by the total)
gt;gt; gt; gt;
gt;gt; gt; gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt;gt; gt; gt;
gt;gt; gt; gt; Any suggestions to improve on this would be most welcome.
gt;gt; gt; gt;
gt;gt; gt; gt; Tel
gt;gt; gt; gt;
With your data in A2:A11 and the type of absence in B2

=SUMPRODUCT(--(LEFT(SUBSTITUTE($A$2:$A$11,quot;CAquot;,quot;^^quot;),LEN(B2))=IF (B2=quot;CAquot;,quot;^^quot;,B2)),--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBST ITUTE(SUBSTITUTE(0amp;SUBSTITUTE($A$2:$A$11,quot;Tquot;,quot;quot;),quot; Squot;,quot;quot;),quot;ALquot;,quot;quot;),quot;CAquot;,quot;^^quot;),quot;Cquot;,quot;quot;),quot;Lquot;,quot;quot;),quot;^^quot;,quot;quot; )))

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},A2amp;quot;012345 6789quot;)),1024)

and copy down
--

Regards,

Peo Sjoblom
quot;Telquot; gt; wrote in message
...
gt; Okay here's a challenge (well it was for me!)
gt;
gt; I am creating a staff planner and need to count part-time staff hours.
gt;
gt; the data that is entered has two variable
gt; a7.5
gt; a = type of absence;
gt; T = training
gt; S = sick
gt; AL = Annual Leave
gt; L = Lieu time
gt; C = compassionate
gt; CA = Carer's Leave
gt;
gt; the number = the number of hours booked off
gt;
gt; How do I seperate the two to make an additional column recognise the
gt; initial
gt; letter and then total all occurences of that letter.
gt;
gt; e.g. (Cell N11)= quot;AL5.5quot; (Cell N15) = quot;AL2.5quot; would show in the Annual
gt; Leave column as 8 hours leave (I would then reduce a further total column
gt; to
gt; by the total)
gt;
gt; If you need to view the planner go to www.tbgb.co.uk/plannerv1.xls
gt;
gt; Any suggestions to improve on this would be most welcome.
gt;
gt; Tel
gt;