Get sum of cells containing "Vacation" and sum by hours

[Patricija]

Hello everyone.

I would need your help. We have an excel table where employees enter their hours, sick days and holidays. Unfortunately, holidays and sick days are not entered in hours, but in words. I need this in hours for the calculation of hours. Is there a formula where I would detect the word VECATION from the list (only one column) and add it together in hours.
So one vacation is 8 hours.

For example: he had 11 days of vacation, which is 88 hours.

I need a formula that will calculate 88 hours from the word Vecation at the end.
Thank you.

 

[etaf]

=countif()
would do that and times by 8/24
cant see what range you are using

example
=COUNTIF(D1:D24,"vacation")*8/24
format cell as [H]:MM

you have vacation in image - but vecation in text , is that a typo

Book7
	A	B	C	D	E	F
1
2				vacation		40:00
3				vacation
4				vacation
5
6
7
8				vacation
9
10
11
12
13
14
15				vacation
16
17
18
19
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIF(D1:D24,"vacation")*8/24

 

[Patricija]

=countif()
would do that and times by 8/24
cant see what range you are using

example
=COUNTIF(D1:D24,"vacation")*8/24
format cell as [H]:MM

you have vacation in image - but vecation in text , is that a typo

Book7
	A	B	C	D	E	F
1
2				vacation		40:00
3				vacation
4				vacation
5
6
7
8				vacation
9
10
11
12
13
14
15				vacation
16
17
18
19
Sheet1

Cell Formulas
Range	Formula
F2	F2	=COUNTIF(D1:D24,"vacation")*8/24

Hey.

The range is AJ11:AJ43.
Yes, it's in text. When employees have a day off, they write me a leave for that day in a word. And that I don't need to calculate the time. I would arrange a formula that detects the word vacation and calculates in hours.

 

[etaf]

so instead of
=COUNTIF(D1:D24,"vacation")*8/24
then use your range
=COUNTIF(AJ11:AJ43,"vacation")*8/24

 

[Patricija]

so instead of
=COUNTIF(D1:D24,"vacation")*8/24
then use your range
=COUNTIF(AJ11:AJ43,"vacation")*8/24

Doesn't work. It calculates me 0.333333333. It should be 8.

 

[Patricija]

Doesn't work. It calculates me 0.333333333. It should be 8.

It's ok now. I changed to =COUNTIF(AJ9:AJ43,"dopust")*8/1.
Thanks for your help.

 

[etaf]

yes as i said you need to format the cell as TIME using [H]:MM
0.33333333 is the fraction of 1 that will be displayed as time
ie 3rd of 1 = 1/3 of 25 hrs = 8

OK , see you have solved to get a value
you are welcome