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)

With much adjusting, I was able to get column M to work for me totaling all
"A". I am having trouble adjusting column O to calculate "S" (range - B8:O8
and B9:O9).

"JLatham" wrote:

While I suggested that adding columns was the better way to do it, my
formulas DO NOT require you to add any columns, they work with the data
exactly as you have shown it in your example. Have you tried the formulas
that I provided. The first one goes into column M, and the second one would
go into column O to calculate the hours.

"something68" wrote:

I'm afraid this will not work for me, there is not enough room to add
columns. I will just have to pay attention and input the total manually :(.

Thanks anyway!

"JLatham" wrote:

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)

Send me a copy of the workbook and I'll try to help with those. I don't need
people's personal information so you can delete names and address and other
similar information, just the real-world values and entries you are working
with in rows 8 and 9 to help. Send it to (remove spaces)
HelpFrom @ JLatham Site .com
and I'll see what I can do with it.

"something68" wrote:

With much adjusting, I was able to get column M to work for me totaling all
"A". I am having trouble adjusting column O to calculate "S" (range - B8:O8
and B9:O9).

"JLatham" wrote:

While I suggested that adding columns was the better way to do it, my
formulas DO NOT require you to add any columns, they work with the data
exactly as you have shown it in your example. Have you tried the formulas
that I provided. The first one goes into column M, and the second one would
go into column O to calculate the hours.

"something68" wrote:

I'm afraid this will not work for me, there is not enough room to add
columns. I will just have to pay attention and input the total manually :(.

Thanks anyway!

"JLatham" wrote:

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)

Wonderful, I will send it right away!

"JLatham" wrote:

Send me a copy of the workbook and I'll try to help with those. I don't need
people's personal information so you can delete names and address and other
similar information, just the real-world values and entries you are working
with in rows 8 and 9 to help. Send it to (remove spaces)
HelpFrom @ JLatham Site .com
and I'll see what I can do with it.

"something68" wrote:

With much adjusting, I was able to get column M to work for me totaling all
"A". I am having trouble adjusting column O to calculate "S" (range - B8:O8
and B9:O9).

"JLatham" wrote:

While I suggested that adding columns was the better way to do it, my
formulas DO NOT require you to add any columns, they work with the data
exactly as you have shown it in your example. Have you tried the formulas
that I provided. The first one goes into column M, and the second one would
go into column O to calculate the hours.

"something68" wrote:

I'm afraid this will not work for me, there is not enough room to add
columns. I will just have to pay attention and input the total manually :(.

Thanks anyway!

"JLatham" wrote:

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)

eMail and attachment received. Oh dear! But I'll do what I can for you with
it.

"something68" wrote:

Wonderful, I will send it right away!

"JLatham" wrote:

Send me a copy of the workbook and I'll try to help with those. I don't need
people's personal information so you can delete names and address and other
similar information, just the real-world values and entries you are working
with in rows 8 and 9 to help. Send it to (remove spaces)
HelpFrom @ JLatham Site .com
and I'll see what I can do with it.

"something68" wrote:

With much adjusting, I was able to get column M to work for me totaling all
"A". I am having trouble adjusting column O to calculate "S" (range - B8:O8
and B9:O9).

"JLatham" wrote:

While I suggested that adding columns was the better way to do it, my
formulas DO NOT require you to add any columns, they work with the data
exactly as you have shown it in your example. Have you tried the formulas
that I provided. The first one goes into column M, and the second one would
go into column O to calculate the hours.

"something68" wrote:

I'm afraid this will not work for me, there is not enough room to add
columns. I will just have to pay attention and input the total manually :(.

Thanks anyway!

"JLatham" wrote:

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)