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)

you may have to insert two new columns next to column A (so you now have two
new blank columns B & C)

enter the below formulas
B8
=LEFT(A8,1)

C8
=RIGHT(A8,1)

B9
=VALUE(LEFT(A9,FIND(",",A9)-1))

C9
=VALUE(RIGHT(A9,LEN(A9)-FIND(",",A9)))


and in your Column M (which is now Column O as we inserted two new columns)
=SUMIF(B8:I9,"=A",B9:I9)

and in your Column O (which is now Column Q as we inserted two new columns)
=SUMIF(B8:I9,"=S",B9:I9)

Regards,
-kc
*Click YES if this works


"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)

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)