SUM numbers in row with leading string character

[strooman]

I want to SUM the numbers in a row which represent the worked hours in a month. The numbers in the cells have a leading string character like V8 or S8 or H7,2. How can I do that. See the mini sheet below for layout and desired results. Thank you very much.

JusKaart.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
19																																	Desired outcome
20	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31		worked	vacation	holiday	sick
21	H7,2	11,0	7,0	7,0	9,0				8,0	7,5						8,0	10,0	7,5	4,0		2,0	7,5	10,0	8,0				2,0	7,5	8,0	8,0		132,00		7,20
22	11,0	7,5		8,0	7,5	8,0	7,5					7,5	8,0	10,0	7,5	7,0		8,0	S8	S8	S8			z8	4,0	8,0		V8					109,50	8,00		24,00
23	V4	V4	V4		V8	V8	V8					V7,2	V7,2	V7,2					V8	V8	V8				8,0	8,0	8,0	8,0	11,0	7,5			50,50	81,60
24			8,0	8,0			H7,2	8,0	H7,2	H7,2	8,0	5,5	7,0		8,0		10,0	8,0		1,5	5,0			10,0	8,0		H7,2		8,0	8,0			111,00		28,80
25	8,0				H7,2		11,0	7,5	6,5	8,0	4,0		4,0	8,0	10,0	8,0		H7,2	4,0			7,5	8,0	11,0	7,5		8,0	H7,2	H7,2	6,5			127,50		28,80
Bert

 

[rajendradk9]

I've assumed your Data begins in Cell A1, just put below formulae starts AF2 Respectively

worked (Column AF)	vacation (Column AG)	holiday (Column AH)	sick (Column AI)
=SUM(A2:AE2)	=COUNTIFS(A2:AE2,"V8")*8+COUNTIFS(A2:AE2,"V4")*4+COUNTIFS(A2:AE2,"V7,2")*7.2	=COUNTIFS(A2:AE2,"H7,2")*7.2	=COUNTIFS(A2:AE2,"S8")*8
=SUM(A3:AE3)	=COUNTIFS(A3:AE3,"V8")*8+COUNTIFS(A3:AE3,"V4")*4+COUNTIFS(A3:AE3,"V7,2")*7.2	=COUNTIFS(A3:AE3,"H7,2")*7.2	=COUNTIFS(A3:AE3,"S8")*8
=SUM(A4:AE4)	=COUNTIFS(A4:AE4,"V8")*8+COUNTIFS(A4:AE4,"V4")*4+COUNTIFS(A4:AE4,"V7,2")*7.2	=COUNTIFS(A4:AE4,"H7,2")*7.2	=COUNTIFS(A4:AE4,"S8")*8
=SUM(A5:AE5)	=COUNTIFS(A5:AE5,"V8")*8+COUNTIFS(A5:AE5,"V4")*4+COUNTIFS(A5:AE5,"V7,2")*7.2	=COUNTIFS(A5:AE5,"H7,2")*7.2	=COUNTIFS(A5:AE5,"S8")*8
=SUM(A6:AE6)	=COUNTIFS(A6:AE6,"V8")*8+COUNTIFS(A6:AE6,"V4")*4+COUNTIFS(A6:AE6,"V7,2")*7.2	=COUNTIFS(A6:AE6,"H7,2")*7.2	=COUNTIFS(A6:AE6,"S8")*8

 

[Peter_SSs]

See if this suits you

23 07 09.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM
20	1	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31		worked	vacation	holiday	sick
21	H7.2	11	7	7	9				8	7.5						8	10	7.5	4		2	7.5	10	8				2	7.5	8	8		132	0	7.2	0
22	11	7.5		8	7.5	8	7.5					7.5	8	10	7.5	7		8	S8	S8	S8			z8	4	8		V8					109.5	8	0	24
23	V4	V4	V4		V8	V8	V8					V7.2	V7.2	V7.2					V8	V8	V8				8	8	8	8	11	7.5			50.5	81.6	0	0
24			8	8			H7.2	8	H7.2	H7.2	8	5.5	7		8		10	8		1.5	5			10	8		H7.2		8	8			111	0	28.8	0
25	8				H7.2		11	7.5	6.5	8	4		4	8	10	8		H7.2	4			7.5	8	11	7.5		8	H7.2	H7.2	6.5			127.5	0	28.8	0
Sum Hours

Cell Formulas
Range	Formula
AJ21:AJ25	AJ21	=SUM(D21:AH21)
AK21:AM25	AK21	=SUMPRODUCT(IFERROR((LEFT($D21:$AH21,1)=LEFT(AK$20,1))*MID($D21:$AH21,2,9),0))

 

[strooman]

Thanks gentlemen, both solutions will do the job. First solution is pretty straight forward and I can comprehend it. The second from Peter; I scratch my head and think what's going on here exactly. I think I'll never master the SUMPRODUCT with all the creative variations. Very slick solution. I'll have to study it to fully comprehend and master it. Nice homework for me. Again, a big thanks to both.

 

[Peter_SSs]

both solutions will do the job.

Does that mean the only possible amount for Sick is 8 hours?
For example, in your sample if instead of vacation 4 hours in cell D24 the person was sick instead of on vacation that cell would say S8 no S4?

Similar with holiday, is the only possibility 7.2 hours?

 

[strooman]

Does that mean the only possible amount for Sick is 8 hours?
For example, in your sample if instead of vacation 4 hours in cell D24 the person was sick instead of on vacation that cell would say S8 no S4?

Similar with holiday, is the only possibility 7.2 hours?

- First, the numbers represent the amount of hours per day. Numbers without a leading string character are the actual working hours that day.
- When an employee has vacation that day we write V8 meaning 8 hours vacation on that day. This can vary when an emplee has a parttime contract. We than can write V4.
- Then about 7,2. In a normal situation somebody works 36 hours a week. When you divide that by 5 (5 working days per week) you get 7,2.
- When an employee is absent due to illness we use the actual amount of hours that person would have worked on that day. For example when he should have worked 8 hours we change that to S8.

Then the answers to your questions.
No, the amount for sick hours depend on what was the actual working hours on that day.
The same is with your question about vacation. If vacation would be V4 but the employee is absent due to illness we record S4.
Last, holiday depends on the contract the employee has and then divides by 5 like I explained above. 36/5 = 7,2 When the contract is 20 hours it's 20/5 = 4.

Your solution is more robust because it automaticaly takes care of all these different situations. Rajendradk has hard-coded the numbers with the leading string character. So I have to adjust his formula when somebody takes 6 hours vacation on a certain day.

 

[Peter_SSs]

When an employee has vacation that day we write V8 meaning 8 hours vacation on that day. This can vary when an emplee has a parttime contract. We than can write V4.

.. or V6 (or possibly other too)

.. when somebody takes 6 hours vacation on a certain day.

I suspected that other amounts would be possible which is why I questioned your comment that "both solutions will do the job"
Anyway, looks like you worked out why I was asking.

Your solution .... automaticaly takes care of all these different situations.