You would have been far better off to have used 2 colums per day, one for A
and one for S rather than trying to combine the two. I suspect that column A
is not the only one that might ever get an "A, S" entry in it with 2 numbers
in the row below.

But, nevertheless, here are two formulas for M and O that would take care of
things IF (big, hairy IF) the "A, S" entry is always "A, S" and not "S, A",
and as long as the numbers as in row 9 are always separated by ", " [a comma
followed by a space].

Now the big, equally hairy and ugly looking formulas:
=SUMIF(A8:I8,"A",A9:I9) +
IF(ISERR(FIND("A,",A8)0),0,VALUE(LEFT(A9,FIND("," ,A9)-1))) +
IF(ISERR(FIND("A,",B8)0),0,VALUE(LEFT(B9,FIND("," ,B9)-1))) +
IF(ISERR(FIND("A,",C8)0),0,VALUE(LEFT(C9,FIND("," ,C9)-1))) +
IF(ISERR(FIND("A,",D8)0),0,VALUE(LEFT(D9,FIND("," ,D9)-1))) +
IF(ISERR(FIND("A,",E8)0),0,VALUE(LEFT(E9,FIND("," ,E9)-1))) +
IF(ISERR(FIND("A,",F8)0),0,VALUE(LEFT(F9,FIND("," ,F9)-1))) +
IF(ISERR(FIND("A,",G8)0),0,VALUE(LEFT(G9,FIND("," ,G9)-1)))

and just so such a hairy, ugly beast doesn't feel lonely, here's a similar
formula for O to get the Sick hours:
=SUMIF(A8:H8,"S",A9:H9) +
IF(ISERR(FIND("A,",A8)0),0,VALUE(RIGHT(A9,LEN(A9)-FIND(" ",A9)))) +
IF(ISERR(FIND("A,",B8)0),0,VALUE(RIGHT(B9,LEN(B9)-FIND(" ",B9))))
+IF(ISERR(FIND("A,",C8)0),0,VALUE(RIGHT(C9,LEN(C9 )-FIND(" ",C9)))) +
IF(ISERR(FIND("A,",D8)0),0,VALUE(RIGHT(D9,LEN(D9)-FIND(" ",D9)))) +
IF(ISERR(FIND("A,",E8)0),0,VALUE(RIGHT(E9,LEN(E9)-FIND(" ",E9)))) +
IF(ISERR(FIND("A,",F8)0),0,VALUE(RIGHT(F9,LEN(F9)-FIND(" ",F9)))) +
IF(ISERR(FIND("A,",G8)0),0,VALUE(RIGHT(G9,LEN(GB9 )-FIND(" ",G9))))

For M:


"something68" wrote:


ANNUAL SICK
A B C D E F G M
N O
Row 8 A, S A A S A S A
Row 9 1.0, 7.0 8.00 8.00 8.00 8.00 8.00 8.00 33.0
0.0 23.0

As you can see, Cell A9 has two leave "hours"; one for A (1.0) and one for S
(7.0).

I would like:

Colmn M, row 9 to show 33.0 (all of the "hours" related to A from row 8); and
Column O, row 9 to show 23.0 (all of the "hours" related to S from row 8)