totalling a sheet when cells contain text and numbers

[conky4]

Hi, I'm using Excel 2010, I have a personnel tracking sheet that tracks an entire year for each employee by day and pay period. If a person calls out sick the cell is coded with SCK or sck and the hours that they are out ex: SCK 8 or sck 10.5 or SCK3.5...(some supervisors put a space after the code some dont...some capitalize some don't) I need to compile from the range of B6:O62 and keep a running total of all hours of sck under a separate cell (N3) within the same sheet.
example of what sheet looks like:
https://www.dropbox.com/s/xm5ygypktljx9ig/personnel sheet test.xlsx

I currently have a "hidden sheet" that totals out for each row...basically a mirror of the other sheet with each cell having a formula similar to this: =IF(LEFT(B6,3)="SCK",VALUE(RIGHT(B6,LEN(B6)-3)),0) and then totaling at the end of the column with: =SUM(Q6:AD6)
This creates a HUGE file because I also need to do the same thing for Kid time and FMLA time...the workbook is already very large with all the employees and if I can narrow it down to 1 formula that totals the whole sheet without creating a separate hidden table, I think it would cut down on the size of my workbook.

 

[conky4]

useful I need to do something similar but just total a row and not the whole sheet. So I've created column "R" that I need to total the OT hours for C4: p4, D4:P4: etc on down the sheet. I need to total just the OT hours that are showing on that row however the sheet is again formatted OT4 or OT 4 or ot4 ot 4...with the text in the same cell as the hour amount. Can you help?

 

[conky4]

Here's a copy of the new sheet with the total column in "R"
https://www.dropbox.com/s/hykjcvx5opw01jh/ot totaling.xlsx?dl=0

 

[Useful]

Hello,

Not sure that i understand exactly what you want. But try this with CSE for the cell R4

=IF(SUM(IFERROR(SEARCH("ot",C4:P4),0)),COUNTIF(C4:P4,"X"),"")

 

[conky4]

No, I need it to count the actual OT hours not the X days. so if the range says ot6 or OT6 or OT 6 or ot 6....(caps lock or spaces) it just totals the number that follows ot for that row. so if c4-p4 is a row for an employee and has OT6 x x x 12 12 12 OT4 x x x 12 12 8+4 on their sheet, at the end it would total just total the OT hours IE: 10 in this example.

 

[Useful]

if you mean that at the same row it is possible to exsist for example "OT6" and "ot 3" and result should be equal to 6+3 =9 ?

if so then try with CSE:

=SUM(IFERROR(ISNUMBER(SEARCH("ot",C4:P4))*SUBSTITUTE(LOWER(C4:P4),"ot",""),0))

 

[conky4]

Yes that is what I mean...I will try it when I get back to work on Monday and let you know! thank you!