Sum of numbers with Prefix?

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

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
 
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
 
You could also use sheet2 to extract the values from sheet1, then total
those numbers up and transfer them back to sheet1.
 
Back
Top