Excel duplicates

[Kariba]

Hopefully an easy one. I've set up a system to count occurrences between two dates which are marked as a "1" and then summed up at the top. However, I don't need to count duplicates, but only duplicates within the same date ranges. I know duplicates can be highlighted or deleted, but there will be other dates when they do occur and need to keep those - this is just one day extract.

Tried a few ways, but can't seem avoid counting these duplicates.

Thanks

Book1
	A	B	C	D	E	F						L
1												12
2
3												Sun
4												7
5		Suf	From	Till	Weekday							12-Nov-23
6	34804		05-11-2023	26-11-2023	SUN	ABC						1
7	39200		12-11-2023	17-12-2023	SUN	ABC						1
8	22054		05-11-2023	19-11-2023	SUN	ABC						1
9	22054		12-11-2023	12-11-2023	SUN	ABC						1
10	12824		05-11-2023	26-11-2023	SUN	ABC						1
11	17220		05-11-2023	24-03-2024	SUN	ABC						1
12	21616		05-11-2023	10-12-2023	SUN	ABC						1
13	26012		05-11-2023	03-12-2023	SUN	ABC						1
14	30408		12-11-2023	19-11-2023	SUN	ABC						1
15	34804		12-11-2023	12-11-2023	SUN	ABC						1
16	39200		12-11-2023	24-03-2024	SUN	ABC						1
17	43596		05-11-2023	26-11-2023	SUN	ABC						1
Sheet2

Cell Formulas
Range	Formula
L1	L1	=SUMIF($F6:$F17,"ABC",L6:L17)
L3	L3	=TEXT(L5,"DDD")
L4	L4	=WEEKDAY(L5,2)
L6:L17	L6	=IF(AND(L$5>=$C6,L$5<=$D6,ISNUMBER(SEARCH(L$3,$E6))),1,0)

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A6:A17	Cell Value	duplicates	text	NO
G6:L229	Cell Value	=0	text	NO
G6:L229	Cell Value	=1	text	NO
G5:L5	Dates Occurring	today	text	NO

 

[Fluff]

How about

=COUNT(UNIQUE(FILTER(A6:A100,L6:L100=1)))

 

[Kariba]

Thanks. Was that going in a helper column? Also would this not count any duplicate between row 6 and row 100 and not just unique ones between the date ranges. The whole file is thousands of rows and changes regularly so trying to get an automatic and accurate daily count.

Book3
	A	B	C	D	E	F						L	M	N	O	P
1												12	0
2
3												Sun	Mon
4												7	1			Expected
5		Suf	From	Till	Weekday							12-Nov-23	13-Nov-23
6	34804		05-11-2023	26-11-2023	SUN	ABC						1		1	1	1
7	39200		12-11-2023	17-12-2023	SUN	ABC						1		1	1	1
8	22054		05-11-2023	19-11-2023	SUN	ABC						1		1	1	1
9	22054		12-11-2023	12-11-2023	SUN	ABC						1		1	1	0
10	12824		05-11-2023	26-11-2023	SUN	ABC						1		0	0	1
11	17220		05-11-2023	24-03-2024	SUN	ABC						1		0	0	1
12	21616		05-11-2023	10-12-2023	SUN	ABC						1		0	0	1
13	26012		05-11-2023	03-12-2023	SUN	ABC						1		0	0	1
14	30408		12-11-2023	19-11-2023	SUN	ABC						1		0	0	1
15	34804		12-11-2023	12-11-2023	SUN	ABC						1		0	0	0
16	39200		12-11-2023	24-03-2024	SUN	ABC						1		0	0	0
17	43596		05-11-2023	26-11-2023	SUN	ABC						1		0	0	1
18	12824		06-11-2023	25-03-2024	MON	ABC						0	1			1
19	17220		06-11-2023	20-11-2023	MON	ABC						0	1			1
20	21616		06-11-2023	25-03-2024	MON	ABC						0	1			1
21	26012		06-11-2023	20-11-2023	MON	ABC						0	1			1
22	30408		06-11-2023	25-03-2024	MON	ABC						0	1			1
23	26012		06-11-2023	25-03-2024	MON	ABC						0	1			0
24	39200		06-11-2023	25-03-2024	MON	ABC						0	1			1
25	43596		06-11-2023	08-01-2024	MON	ABC						0	1			1
26	47992		06-11-2023	08-01-2024	MON	ABC						0	1			1
27	12824		06-11-2023	25-03-2024	MON	ABC						0	1			0
28	56784		13-11-2023	01-01-2024	MON	ABC						0	1			1
Sheet2

Cell Formulas
Range	Formula
L1:M1	L1	=SUMIF($F6:$F17,"ABC",L6:L17)
L3:M3	L3	=TEXT(L5,"DDD")
L4:M4	L4	=WEEKDAY(L5,2)
L6:L17,L18:M28	L6	=IF(AND(L$5>=$C6,L$5<=$D6,ISNUMBER(SEARCH(L$3,$E6))),1,0)
N6:N17	N6	=COUNT(UNIQUE(FILTER($A6:$A$100,$L6:$L$100=1)))
O6:O17	O6	=COUNT(UNIQUE(FILTER(A6:A100,L6:L100=1)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A18:A28	Cell Value	duplicates	text	NO
A6:A17	Cell Value	duplicates	text	NO
A18:A28	Cell Value	duplicates	text	NO
M18:M28	Cell Value	=0	text	NO
M18:M28	Cell Value	=1	text	NO
L6:M17	Cell Value	=0	text	NO
L6:M17	Cell Value	=1	text	NO
G29:M229,G6:K17,G18:L28	Cell Value	=0	text	NO
G29:M229,G6:K17,G18:L28	Cell Value	=1	text	NO
G5:M5	Dates Occurring	today	text	NO

 

[Fluff]

That formula goes in L1, not a helper column.

 

[Kariba]

Thanks. I had tried that initially but was getting unexpected result.

Have since realised that some of the data in column A was not in number format so all good now once converted.

 

[Fluff]

You're welcome & thanks for the feedback.