Finding the average based on Yes or No

[StillUnderstanding]

Hello,

I wonder if anyone will be able to help me with this.

I am try to work out how many hours you should work per day. So if I am contracted to work 37 hours (A4) and I have Yes in E2 to I2 then I would expect to see 7.24 in E4 to I4. If However I only say yes to 4 of the 5 days (E3 to H3) then I would expect to see 9.25 on (E4 to H4) and 0 in I4.

I can't work out the formula. Would someone please be able to help?

This is as close as I have been able to get it =$B$4/COUNTIF($F$3:$J$3,"Yes") but its showing 37 in each cell (E4 to I4).

Thank you.

1​	A	B	C	D	E	F	G	H	I
2​		Work Day			Monday	Tuesday	Wednesday	Thursday	Friday
3​	Contracted Hours	Is this a working Day?			Yes	Yes	Yes	Yes	Yes
4​	37.00	Hours Worked			7.24	7.24	7.24	7.24	7.24

 

[nemmi69]

Try

=IF(E2="yes",$A$3/COUNTIF($E$2:$I$2,"yes"),0)

 

[etaf]

=$B$4/COUNTIF($F$3:$J$3,"Yes")

working for me

Book1
	A	B	C	D	E	F	G	H	I	J	K
1			Work Day				Monday	Tuesday	Wednesday	Thursday	Friday
2	Contracted Hours	Is this a working Day?					Yes	Yes	Yes
3	37	Hours Worked					7.24	7.24	7.24	7.24	7.24
4
5							12.3333333	12.3333333	12.3333333	12.3333333	12.3333333
Sheet1

Cell Formulas
Range	Formula
G5:K5	G5	=$A$3/COUNTIF($G$2:$K$2,"Yes")

 

[nemmi69]

Better option to avoid typos
In E3
=IF(UPPER(E2)="YES",$A$3/UPPER(COUNTIF($E$2:$I$2,"YES")),0)

 

[StillUnderstanding]

Thank you @nemmi69 that works perfect!

 

[nemmi69]

No problem