Count columns if they have data, except duplicates

[EmmaFos]

Hello out there!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C5 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 G23:G27 in Sheet1 is 36 across a 26 day block - it's the 26 days I need to C5 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
	Y
25
Sheet1

Schedule_Test.xlsx
	E
5
Sheet2

Cell Formulas
Range	Formula
E5	E5	=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),"")

 

[danielphayward]

Could you include a larger range in your example workbook?

 

[EmmaFos]

Hello out there!
Hoping someone may help, I am scheduling blocks of work by week and need a formula to divide C5 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 G23:G27 in Sheet1 is 36 across a 26 day block - it's the 26 days I need to C5 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
	Y
25
Sheet1

Schedule_Test.xlsx
	E
5
Sheet2

Cell Formulas
Range	Formula
E5	E5	=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),"")

Hi,
Thanks for responding!
Sorry, yes - I have edited my original post with the correct range

 

[EmmaFos]

Hi,
Thanks for responding!
Sorry, yes - I have edited my original post with the correct range

Hi again,
Thanks, it looks like the update of this post didn't go through, this is the correct example workbook - I've shortened the range to formula

Appreciate your help

=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
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
4	BL02	10734.35	335.4484375
5	BL03	7871.65	245.9890625
6	BL04	7022.85	219.4640625
7	BL05	9752	304.75
Sheet2

Cell Formulas
Range	Formula
B3	B3	=SUM(Sheet1!D3:D11)
C3:C7	C3	=B3/32
B4	B4	=SUM(Sheet1!D12:D22)
B5	B5	=SUM(Sheet1!D23:D29)
B6	B6	=SUM(Sheet1!D30:D37)
B7	B7	=SUM(Sheet1!D40:D48)
E3:T3	E3	=IF(OR(Sheet1!G$3=1,Sheet1!G$4=1),Sheet2!$C$3/SUM(Sheet1!$G$3:$W$4),"")
E4:T4	E4	=IF(OR(Sheet1!G$12=1,Sheet1!G$13=1,Sheet1!G$14=1,Sheet1!G$1=1,Sheet1!G$16=1),Sheet2!$C$4/SUM(Sheet1!$G$12:$EB$16),"")
E5:T5	E5	=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),"")
E6:T6	E6	=IF(OR(Sheet1!G$30=1,Sheet1!G$31=1,Sheet1!G$32=1,Sheet1!G$33=1,Sheet1!G$34=1),Sheet2!$C$6/SUM(Sheet1!$G$30:$EB$34),"")
E7:T7	E7	=IF(OR(Sheet1!G$40=1,Sheet1!G$41=1,Sheet1!G$42=1,Sheet1!G$43=1,Sheet1!G$44=1),Sheet2!$C$7/SUM(Sheet1!$G$40:$EB$44),"")

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
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
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
5	BL01	20	4	145.15
Sheet1

Cell Formulas
Range	Formula
B3:B5	B3	=C3*5
C3:C5	C3	=SUM(G3:DS3)

 

[EmmaFos]

Hi,
Any suggestions from an awesome individual out there?

Thanks