Add cells with text and numbers in same cells based on the text in the same cell

[steve1979]

I have been trying to find a way to add the values in cells, but the cells contain text as well as numbers. The sheet is to calculate overtime. i can get it to count the cells with the text in it and from there i multiply by 12 to get the hours worked, but i need to add number to the same cell in the case that person does not work 12 hours see example:

17 oct to 16 november overtime.xlsm
	A	B	C	D	E	F
1	Date	17-Dec	18-Dec	19-Dec	20-Dec	21-Dec
2	Day	Thu	Fri	Sat	Sun	Mon
3	Day Shift
4	O/T Person
5	O/T Person			person A 12
6	O/T Person
7	Night Shift
8	O/T Person
9	O/T Person
10	O/T Person
11
12	Date	22-Dec	23-Dec	24-Dec	25-Dec	26-Dec
13	Day	Tue	Wed	Thu	Fri	Sat
14	Day Shift
15	O/T Person					person A 8
16	O/T Person
17	O/T Person
18	Night Shift
19	O/T Person
20	O/T Person
21	O/T Person
22			Name	Ctrl No:	Days	Hours
23			person A		2	24
test sheet

Cell Formulas
Range	Formula
C12:F12,C1:F1	C1	=B1+1
B13:F13,B2:F2	B2	=TEXT(B1,"ddd")
B12	B12	=F1+1
E23	E23	=SUM(COUNTIF($B$3:$F$6,"*"&C23&"*"),COUNTIF($B$8:$F$10,"*"&C23&"*"),COUNTIF($B$15:$F$17,"*"&C23&"*"),COUNTIF($B$19:$F$21,"*"&C23&"*"))
F23	F23	=E23*12

Person A worked 2 days ( one he worked for 12 hours and next 8 hours the total should be 10 but my calculation gives me 24. ( 2 days *12)
i need it to add the hours worked based on the persons name and hours inserted. if no hours inserted it should default to 12.
a formula i tried does not work, it reads false and reverts to days worked *12.
the formula ( was on different sheet) the ranges are 4 different ranges
=SUM(IF(ISNUMBER(FIND(C33;SearchRng));VALUE(LEFT(SearchRng;FIND(C33;SearchRng)-1));E33*12))

 

[ExcelGzh]

What you are trying to do could be done with a massively convoluted formula that would be almost impossible to debug. I suggest you have two columns for each date. Left column is the person's name, right column is the hours. Then F23 would contain 5 SUMIFS functions (one for each pair of columns) along the lines of =SUMIFS(C3:C22,B3:B22,F23) + SUMIFS(... This assumes that you have inserted the additional columns and what is in your example as column C would then be column D. I prefer to use SUMIFS even though there is only one criteria because it puts the sum range first instead of last like SUMIF which means if you want to add a second criteria later you just add it at the end instead of have to re-jig the whole thing changing from SUMIF to SUMIFS.