Adding cells with numbers and a letter designation to specific cells

[GHOSTOF309]

Hello all,

I am somewhat of a novice at Excel but have been dabbling in it over the past few years learning on my own.
I have made monthly report for employees at work to include rows of cells for hours worked each day and time off used each day (vacation, personal, sick, etc). If you see in the first picture below, duty hours will always just be a number equalling the number of hours worked. The "other" hours would be listed as the number of hours taken off of work along with a letter indicating what type of time was used to take off... ex: 4 hours vacation time would be 4V, 4 hours personal time would be 4P and so on. I have figured out the macro that I needed to be able to add cells that include text using sum_only_numbers which works well for adding horizontally and vertically.
At the bottom of my worksheet i have cells for the total amount of Vacation, Personal, Sick time etc. used for the month. I am trying to figure out how to make a formula for each cell under Personal, Sick, Vacation etc in picture #2 to add the cells in picture #1 automatically determined by the letter used 4P, 4V, 4S etc. I have found numerous formulas but none seem to work.
Thanks in advance for any and all assistance.

 

[kweaver]

How about something like the following (which also doesn't use VBA for the total line):

Book1
	A	B	C	D	E	F	G	H	I	J
1	Shift
2	Duty				1		3			3 E
3	Other		10 S		2 V	2 S	2		4 V
4	Other	1 S	2 S					5 S	4 V
5	Total	1	12	0	2	2	0	5	8	3
6
7
8		S - Sick	E- Education	V - Vacation
9		20	3	10
Sheet3

Cell Formulas
Range	Formula
B5:J5	B5	=SUM(IFERROR(0+TEXTBEFORE(B2:B4," "),0))
B9:D9	B9	=SUM(IFERROR(0+TEXTBEFORE($B$2:$J$5," "&LEFT(B8,1)),0))

Made an edit for 2-digit number possibilities.

 

[kweaver]

Sorry about the error in previous post (#2). I think think this is now correct and has the total for each row.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Shift										TOTAL
2	Duty				1		3			3 E	7
3	Other		10 S		2 V	2 S	2		4 V		20
4	Other	1 S	2 S					5 S	4 V		12
5	Total	1	12	0	3	2	5	5	8	3	39
6
7
8		S - Sick	E- Education	V - Vacation
9		20	3	10
Sheet3

Cell Formulas
Range	Formula
K2:K4	K2	=SUM(IFERROR(ISNUMBER(B2:J2)*B2:J2,0))+SUM(IFERROR((0+TEXTBEFORE(B2:J2," ")),0))
B5:K5	B5	=SUM(IFERROR(ISNUMBER(B2:B4)*B2:B4,0))+SUM(IFERROR(0+TEXTBEFORE(B2:B4," "),0))
B9:D9	B9	=SUM(IFERROR(0+TEXTBEFORE($B$2:$J$5," "&LEFT(B8,1)),0))

 

[GHOSTOF309]

Sorry about the error in previous post (#2). I think think this is now correct and has the total for each row.

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	Shift										TOTAL
2	Duty				1		3			3 E	7
3	Other		10 S		2 V	2 S	2		4 V		20
4	Other	1 S	2 S					5 S	4 V		12
5	Total	1	12	0	3	2	5	5	8	3	39
6
7
8		S - Sick	E- Education	V - Vacation
9		20	3	10
Sheet3

Cell Formulas
Range	Formula
K2:K4	K2	=SUM(IFERROR(ISNUMBER(B2:J2)*B2:J2,0))+SUM(IFERROR((0+TEXTBEFORE(B2:J2," ")),0))
B5:K5	B5	=SUM(IFERROR(ISNUMBER(B2:B4)*B2:B4,0))+SUM(IFERROR(0+TEXTBEFORE(B2:B4," "),0))
B9:D9	B9	=SUM(IFERROR(0+TEXTBEFORE($B$2:$J$5," "&LEFT(B8,1)),0))

KWEAVER - YOU ARE MY FAVORITE PERSON TODAY! IT WORKED! THANK YOU VERY MUCH, I HAVE BEEN PULLING MY HAIR OUT WITH THIS.

 

[kweaver]

Glad it all worked for you. Hope you still have some hair.