Count Unique Values Based on Unique (sort of) Criteria

[Minx_Minxy]

Apologies if this question has been raised before, I did look through the old posts but nothing that got close to an answer for me.

I need the count of how many times the ID occurs during a period. But I need to count them uniquely.

Here is an example of the data

Date	ID
22/07/2024	5239926
22/07/2024	5239926
22/07/2024	5129989
23/07/2024	5129989
24/07/2024	5239926
24/07/2024	5239926
24/07/2024	5245569
26/07/2024	5129989
27/07/2024	5245569

This is the formula I am using

=COUNTIFS([ID],A1,[Date],"<="&A2)

Where A1 = the ID I am filtering
and A2 = Today's date

It works fine for unique IDs such as 5129989, but for IDs that repeat during the day, like ID 5239926 , it gives me 4, but really I need it to give me 2.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D
1	5239926		Date	ID
2	02/08/2024		22/07/2024	5239926
3	2		22/07/2024	5239926
4			22/07/2024	5129989
5			23/07/2024	5129989
6			24/07/2024	5239926
7			24/07/2024	5239926
8			24/07/2024	5245569
9			26/07/2024	5129989
10			27/07/2024	5245569
Data

Cell Formulas
Range	Formula
A2	A2	=TODAY()
A3	A3	=ROWS(UNIQUE(FILTER(C2:C100,(D2:D100=A1)*(C2:C100<=A2))))

 

[etaf]

how about
=COUNTA(UNIQUE(FILTER(B1:B9,(B1:B9<=A2)*(C1:C9=A1))))

Book11
	A	B	C	D	E
1	5239926	7/22/24	5239926	2
2	8/2/24	7/22/24	5239926
3		7/22/24	5129989
4		7/23/24	5129989
5		7/24/24	5239926
6		7/24/24	5239926
7		7/24/24	5245569
8		7/26/24	5129989
9		7/27/24	5245569
Sheet1

Cell Formulas
Range	Formula
D1	D1	=COUNTA(UNIQUE(FILTER(B1:B9,(B1:B9<=A2)*(C1:C9=A1))))
A2	A2	=TODAY()

 

[Minx_Minxy]

Thank you both! Both formulas work! I would have put both as the solution if I could.

Thank you so much for helping out

 

[Fluff]

Glad we could help & thanks for the feedback.