Sum of numbers with Prefix?

[news.microsoft.com]

Hi,

We've got a sheet with a list of employees (Row) and dates (Columns) - in
each cell there can be a particular code relating to the attendance of that
employee.

for example:
1st 2nd 3rd 4th
Joe Bloggs L#30 / H L#22

L#30 means that Joe was 30 minutes late !
/ = on time
H= Holiday etc etc (there are about 20 codes)

at the end of these rows are a few summary cells which show total number of
holidays in the month and total number of lates (both using countIf)

I need to add a 3rd summary column which shows how many minutes the person
was late in the month..

so in the example above the Summary would be:

Hols Lates Mins
1 2 55

I'm struggling with the process of (if cell starts with L# then add the
numeric part)

Is this possible using standard functions? or would I need to to this in
VBA?

TIA

Phil

 

[RagDyeR]

You could try this *array* formula:

=SUM(IF(LEFT(A1:J1,2)="L#",--MID(A1:J1,3,5)))

Array formulas are entered using CSE, <Ctrl> <Shift> <Enter>, instead of the
regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


message Hi,

We've got a sheet with a list of employees (Row) and dates (Columns) - in
each cell there can be a particular code relating to the attendance of that
employee.

for example:
1st 2nd 3rd 4th
Joe Bloggs L#30 / H L#22

L#30 means that Joe was 30 minutes late !
/ = on time
H= Holiday etc etc (there are about 20 codes)

at the end of these rows are a few summary cells which show total number of
holidays in the month and total number of lates (both using countIf)

I need to add a 3rd summary column which shows how many minutes the person
was late in the month..

so in the example above the Summary would be:

Hols Lates Mins
1 2 55

I'm struggling with the process of (if cell starts with L# then add the
numeric part)

Is this possible using standard functions? or would I need to to this in
VBA?

TIA

Phil

 

[Keith Streich]

You could also use sheet2 to extract the values from sheet1, then total
those numbers up and transfer them back to sheet1.

 

[news.microsoft.com]

Thanks RD

This worked perfectly !!

Phil

 

[RagDyer]

Appreciate the feed-back.