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