Sum text that contains numbers - But ONLY the numbers.

[Sumitup8]

Hi all,

I am trying to do a planning schedule, which includes different earnings, such as; sick days, holiday earned, lieue days earned/taken, compensation earned/taken. The hoiday earned, and sick days were very simple because all I did was color code "H" for Holiday, and to the very right had a column in which it counted the "H"for holidays so that we know how many remains.

I encountered the problem when I need to sum hours earned in different ways, such as Lieue Hours, and Compensation Hours.

LE+amount of hours = LE1.25, and so on. LE = Lieue Earned.
LT+amount of hours = LT1.25, and so on. LT = Lieue Taken.
CT+amount of hours = CT1.25, and so on. CT = Compensation Taken.
CE+Amount of hours = CE1.25, and so on. CE = Compensation Earned.

[TABLE="class: grid, width: 350"]
<tbody>[TR]
[TD]

Name
​

[/TD]
[TD]1 May
[/TD]
[TD]2 May
[/TD]
[TD]3 May
[/TD]
[TD]4 May
[/TD]
[TD]5 May
[/TD]
[TD]6 May
[/TD]
[TD]7 May
[/TD]
[TD]Lieue Earned
[/TD]
[TD]Lieue Taken
[/TD]
[TD]Comp. Earned
[/TD]
[TD]Comp. Taken
[/TD]
[/TR]
[TR]
[TD]Peter
[/TD]
[TD]CE2.25
[/TD]
[TD][/TD]
[TD][/TD]
[TD]CT1.25
[/TD]
[TD][/TD]
[TD]LE4.25
[/TD]
[TD]LT3.25
[/TD]
[TD]4.25
[/TD]
[TD]3.25
[/TD]
[TD]2.25
[/TD]
[TD]1.25
[/TD]
[/TR]
[TR]
[TD]Pan
[/TD]
[TD][/TD]
[TD]CE1.25
[/TD]
[TD]CT1
[/TD]
[TD][/TD]
[TD][/TD]
[TD]LE4
[/TD]
[TD]LT4
[/TD]
[TD]4
[/TD]
[TD]4
[/TD]
[TD]1.25
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]Rose
[/TD]
[TD]CE1.5
[/TD]
[TD]CT1.5
[/TD]
[TD][/TD]
[TD]LE5
[/TD]
[TD]LT4
[/TD]
[TD][/TD]
[TD][/TD]
[TD]5
[/TD]
[TD]4
[/TD]
[TD]1.5
[/TD]
[TD]1.5
[/TD]
[/TR]
[TR]
[TD]Bert
[/TD]
[TD][/TD]
[TD]CE1.25
[/TD]
[TD]CT0.5
[/TD]
[TD][/TD]
[TD]LE5
[/TD]
[TD]LT5
[/TD]
[TD][/TD]
[TD]1.25
[/TD]
[TD]0.5
[/TD]
[TD]LE5
[/TD]
[TD]LT5
[/TD]
[/TR]
</tbody>[/TABLE]

I have scowered the net but unable to find anything that helps. I tried the sum if right / left code but didnt get it to work..

Please, if anyone has tips for me?

 

[Ron Coderre]

I restructured your data a bit

Using this:
  A       B        C        D        E        F        G        H        I             J            K             L
1                                                                        LE            LT           CE            CT
2 Name    1-May    2-May    3-May    4-May    5-May    6-May    7-May    Lieue_Earned  Lieue_Taken  Comp._Earned  Comp._Taken
3 Peter   CE2.25                     CT1.25            LE4.25   LT3.25   4.25          3.25         2.25          1.25
4 Pan              CE1.25  CT1                         LE4      LT4      4.00          4.00         1.25          1.00
5 Rose    CE1.5    CT1.5             LE5      LT4                        5.00          4.00         1.50          1.50
6 Bert             CE1.25  CT0.5              LE.5     LT.5              1.25          0.50         0.50          0.50

J3: =SUMPRODUCT(REPLACE($B3:$H3,1,2,"0")*(LEFT($B3:$H3,2)=J$1))

Copy J3 down through J6


Copy J3:J6 and Paste into L3:L6


Is that something you can work with?

 

[Sumitup8]

Hi Ron,

Thank you for your reply.

It did not work as expected, however I believe it is user error from my side.

In case anyone has a similar issue, you can try to go around it like this;
=SUMIF(B1:I1, ">0") .. --- So it will sum anything above 0 (ZERO) to a column.
=SUMIF(B1:I1, "<0") .. --- So it will sum anything below 0 (ZERO) to a column.




[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name:

[/TD]
[TD]5/1-19

[/TD]
[TD]5/2-19
[/TD]
[TD]5/3-19
[/TD]
[TD]5/4-19
[/TD]
[TD]5/5-19
[/TD]
[TD]5/6-19
[/TD]
[TD]5/7-19
[/TD]
[TD]Comp Earned
[/TD]
[TD]Comp Taken

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Slavko

[/TD]
[TD]0.25[/TD]
[TD]0.5[/TD]
[TD][/TD]
[TD]-0.5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75
[/TD]
[TD]-0.5[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Milica
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75[/TD]
[TD][/TD]
[TD][/TD]
[TD]-0.25[/TD]
[TD][/TD]
[TD]0.75[/TD]
[TD]-0.25[/TD]
[/TR]
</tbody>[/TABLE]

Then I have formatted the cells to turn into a specific color if the number is <0.20 and another color if it is >0.20.

This gave me the result I was looking for.

Thank you and have a nice weekend!

 

[Sumitup8]

I made a mistake, please refer to below table and explanation;

(should be inputed in I1) =SUMIF(B1:H1, ">0") .. --- So it will sum anything above 0 (ZERO) to a column.
(should be inputed in I1) =SUMIF(B1:H1, "<0") .. --- So it will sum anything below 0 (ZERO) to a column.

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[TD]G
[/TD]
[TD]H
[/TD]
[TD]I
[/TD]
[TD]J
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Name:

[/TD]
[TD]5/1-19

[/TD]
[TD]5/2-19
[/TD]
[TD]5/3-19
[/TD]
[TD]5/4-19
[/TD]
[TD]5/5-19
[/TD]
[TD]5/6-19
[/TD]
[TD]5/7-19
[/TD]
[TD]Comp Earned
[/TD]
[TD]Comp Taken

[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]Slavko

[/TD]
[TD]0.25
[/TD]
[TD]0.5
[/TD]
[TD][/TD]
[TD]-0.5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75
[/TD]
[TD]-0.5
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]Milica
[/TD]
[TD][/TD]
[TD][/TD]
[TD]0.75
[/TD]
[TD][/TD]
[TD][/TD]
[TD]-0.25
[/TD]
[TD][/TD]
[TD]0.75
[/TD]
[TD]-0.25
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sumitup8]

I made a mistake, please refer to below table and explanation;

Jesus. Saw I made another mistake..

Should be B2, H2, I2, and J2..

:D