Count data in column, ignore duplicates

[EmmaFos]

Hello out there!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C3 by count rows in sheet1 & insert into the respective date. The below formula works perfectly when summing all the numbers, the issue I have is when blocks double up in a date column, I need the duplicates to be ignored or counted across the dates.
IE, the sum of G3:G4 in Sheet1 is 24 across a 16 day block - it's the 16 days I need to C3 divided by and I'm not quite sure how to include that in my formula?

I would really appreciate the help!

=IF(OR(Sheet1!G$23=1,Sheet1!G$24=1,Sheet1!G$25=1,Sheet1!G$26=1,Sheet1!G$27=1),Sheet2!$C$5/SUM(Sheet1!$G$23:$EB$27),"")

Schedule_Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
2		Hours	Numbers		6-Jun-22	13-Jun-22	20-Jun-22	27-Jun-22	4-Jul-22	11-Jul-22	18-Jul-22	25-Jul-22	1-Aug-22	8-Aug-22	15-Aug-22	22-Aug-22	29-Aug-22	5-Sep-22	12-Sep-22	19-Sep-22
3	BL01	3939.35	123.1046875		5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5
Sheet2

Cell Formulas
Range	Formula
B3	B3	=SUM(Sheet1!D3:D11)
C3	C3	=B3/32
E3:T3	E3	=IF(OR(Sheet1!G$3=1,Sheet1!G$4=1),Sheet2!$C$3/SUM(Sheet1!$G$3:$W$4),"")

Schedule_Test.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2		Durations Days	Durations Weeks	Hours	Numbers		6-Jun-22	13-Jun-22	20-Jun-22	27-Jun-22	4-Jul-22	11-Jul-22	18-Jul-22	25-Jul-22	1-Aug-22	8-Aug-22	15-Aug-22	22-Aug-22	29-Aug-22	5-Sep-22	12-Sep-22	19-Sep-22	26-Sep-22
3	BL01	40	8	1016.05			1	1	1	1	1	1	1	1
4	BL01	120	24	290.3			1	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
Sheet1

Cell Formulas
Range	Formula
B3:B4	B3	=C3*5
C3:C4	C3	=SUM(G3:DS3)

 

[Fluff]

Duplicate Count columns if they have data, except duplicates

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).