Hi Mike,
Wow!! That works perfectly. I have a follow on question though.
I have a second formula that calculates if the "holiday" has been taken
taking into account the current date (which is held in cell A2) and if it has
passed my column header dates held in row 2 from C2 onwards. This formula is
as follows:
=SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:CN3)-1)*1*($C$2:$CN$2<=$A$2)))
How do I amend this to incorporate your =addtime(C3:CN3,"H") formula?
Currently its giving me a VALUE error bcause of the merging in one cell of
for example H3.5L4.5.
Thanks.
--
PK wilts


"Mike H" wrote:

Hi,

How about a user defined function. Alt+F11 to open VB editor. Right click
'ThisWorkbook' and insert module and paste the code below in

call the code with

=addtime(rng,"L")

where rng is the range to count and "L" is the letter to sum so for your
example

=addtime(C3:G3,"h")


Function addtime(rng As Range, ltr As String)
Dim ts As String
Dim x As Long
ltr = UCase(ltr)
For Each c In rng
If InStr(UCase(c.Value), ltr) 0 Then
For x = InStr(UCase(c.Value), ltr) To Len(c.Value)
If IsNumeric(Mid(c.Value, x, 1)) Or Mid(c.Value, x, 1) = "." Then
ts = ts + Mid(c.Value, x, 1)
If Not IsNumeric(Mid(c.Value, x + 1, 1)) And _
Mid(c.Value, x + 1, 1) < "." Then Exit For
End If
Next
End If
If ts < "" Then
addtime = addtime + Val(ts)
ts = ""
End If
Next
End Function

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"PK" wrote:

I have various cells some of which hold an alpha-number field, some hold an
alpha-number-alpha-number field, some are blank. These range for example from
cells C3:G3 as follows starting at C3
H7.5 blank H3.5L4.5 blank L7.5
I would like to sum all numbers prefixed with H and seprately, all prefixed
with L
Hence the result from above should read - Total for H is 11 Total for L is
12.
I have 2 separate formulas as follows which work until you input an H value
and an L value into the same field.
=SUM(IF(LEFT(C3:G3)="H",RIGHT(C3:G3,LEN(C3:G3)-1)*1))
=SUM(IF(LEFT(C3:G3)="L",RIGHT(C3:G3,LEN(C3:G3)-1)*1))
I am aware this is an array formula and one must complete the shift control
enter to obtain curved brackets around the formula.
Any assistance to solve my issue would be very much appreciated.
For info H = holiday taken. L = Lieu time taken.
On some days the staff merge H with acquired L to make up a day off.
Thanks,
PK
--
PK wilts