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.

 

[Trevor G]

I have had a brief look at your spreadsheet, what I did was add an extra row below the entries and used the following formula to extract the numbers. I hope this helps you find a solution.

Excel Workbook
	F	G	H	I
4	MON	TUE	WED	THU
5	16-Dec	17-Dec	18-Dec	19-Dec
6	ot8	ot 12	ot10	OT 4
7	8	12	10	4
personnel sheet

 

[Useful]

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.

Hi! first unmerge the 3 rd row and try this formla with CSE
(For cell N3)

=SUM(IF(NOT(ISERROR(--(TRIM(SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(N2,1,SEARCH(" ",N2)-1)),""))))),--(TRIM(SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(N2,1,SEARCH(" ",N2)-1)),"")))*($A$6:$A$62="")))

in this case you may copy/paste above formula for the cells H3,J3 and L3.

 

[conky4]

Hi! first unmerge the 3 rd row and try this formla with CSE
(For cell N3) =SUM(IF(NOT(ISERROR(--(TRIM(SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(N2,1,SEARCH(" ",N2)-1)),""))))),--(TRIM(SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(N2,1,SEARCH(" ",N2)-1)),"")))*($A$6:$A$62="")))

in this case you may copy/paste above formula for the cells H3,J3 and L3.

This worked perfectly for the sck n3. I tried putting it in for j3 and l3 and it doesn't total anything. I copied formula exactly as is and it computed the sck time, so I changed all the N2 to J2 and L2 respectively and it put zeros' what did I enter wrong?

 

[conky4]

OK it must have been my computer because I closed it and tried again and it all worked!! Thank you USEFUL!

 

[conky4]

Hi Useful...Originally this formula worked when I didn't have my sheets completed. Now when I've added in the schedules it is totaling their reg work hours along with kid and fmla etc Is there a way to adjust this so it doesn't count those hours? I will upload a completed copy of a personnel sheet to further explain. The sheets show their reg shifts ie xxx as days off and 12 12 12 8+4 as the work hrs. Ot would be reflected as OTwith the number of hours IE OT12 or OT8

 

[conky4]

https://www.dropbox.com/s/w4hsaiu4h99qqas/SHIFT AND VACATION SELECTION 2014.xlsx TEST 2B.xlsx

 

[Useful]

Hi,
try this for cell N3
with CSE
=SUM(IFERROR(IF(--NOT(ISERROR(SEARCH(LOWER(MID(N2,1,SEARCH(" ",N2)-1)),$B$6:$O$62))),SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(N2,1,SEARCH(" ",N2)-1)),"")*($A$6:$A$62="")),))

 

[conky4]

That worked perfectly. Now only tweek left...is it possible when applying it to the OT (H2) is it possible to have it just total to current date instead of the whole sheet?

 

[Useful]

That worked perfectly. Now only tweek left...is it possible when applying it to the OT (H2) is it possible to have it just total to current date instead of the whole sheet?

Hi again!

for cell H3 (also can be applied the other cells as well)

(with CSE)

=SUM(IFERROR(IF(NOT(ISERROR(SEARCH(LOWER(MID(H2,1,SEARCH(" ",H2)-1)),$B$6:$O$62)))*IF(($A$5:$A$62<>""),($B$5:$O$62)<=TODAY()),SUBSTITUTE(LOWER($B$6:$O$62),LOWER(MID(H2,1,SEARCH(" ",H2)-1)),"")*($A$6:$A$62="")),))