Count unique value appear based on date range

[blader1989]

Hi all,

I'm looking for formular that can count unique value appear in range of date from my report, I have tried a lot of method but it could not result as my desired. So it would be great from your help to solve my problem.

Below is my example table and desired output I would to have: Consider that I receive the report from a server then I want to count the unique value appear in date range, if this value appear (assump value X appear on 25 and 26 March 2025, it will count as 2 not one EVEN THOUGH it appear 2 times on 25 March 2025).

Book1
	A	B
1	Date	Value
2	25/03/2025	X
3	25/03/2025	X
4	26/03/2025	X
5	25/03/2025	Y
6	25/03/2025	Y
7	27/03/2025	Y
8	26/03/2025	Y
9	26/03/2025	Z
10	27/03/2025	Z
11	25/03/2025	Z
12
13	Desired output as below
14	Value	Count
15	X	2
16	Y	3
17	Z	3
Sheet4

Cell Formulas
Range	Formula
A15:A17	A15	=UNIQUE(B2:B11)
Dynamic array formulas.

Thank you so much, your help will be much appreciated!!!

 

[Sanjeev1976]

Hello,

You can try the below to get the desired results :

Book4
	A	B
1	Date	Value
2	25/03/2025	X
3	25/03/2025	X
4	26/03/2025	X
5	25/03/2025	Y
6	25/03/2025	Y
7	27/03/2025	Y
8	26/03/2025	Y
9	26/03/2025	Z
10	27/03/2025	Z
11	25/03/2025	Z
12
13	Desired output as below
14	Value	Count
15	X	2
16	Y	3
17	Z	3
Sheet1

Cell Formulas
Range	Formula
A15:A17	A15	=UNIQUE(B2:B11)
B15:B17	B15	=SUMPRODUCT(--(CHOOSECOLS(UNIQUE($A$2:$B$11),2)=A15))
Dynamic array formulas.

 

[shaowu459]

工作簿1
	A	B	C	D
1	Date	Value
2	2025-3-25	X
3	2025-3-25	X
4	2025-3-26	X
5	2025-3-25	Y
6	2025-3-25	Y
7	2025-3-27	Y
8	2025-3-26	Y
9	2025-3-26	Z
10	2025-3-27	Z
11	2025-3-25	Z
12
13	Desired output as below
14	Value	Count
15	X	2	2
16	Y	3	3
17	Z	3	3
18
Sheet1

Cell Formulas
Range	Formula
A15:A17	A15	=UNIQUE(B2:B11)
C15:C17	C15	=SUM(N(UNIQUE(A$2:B$11)=A15))
Dynamic array formulas.

 

[shaowu459]

工作簿1
	A	B	C
1	Date	Value
2	2025-3-25	X
3	2025-3-25	X
4	2025-3-26	X
5	2025-3-25	Y
6	2025-3-25	Y
7	2025-3-27	Y
8	2025-3-26	Y
9	2025-3-26	Z
10	2025-3-27	Z
11	2025-3-25	Z
12
13	Desired output as below
14	Value	Count
15	X	2
16	Y	3
17	Z	3
18
Sheet1

Cell Formulas
Range	Formula
A15:B17	A15	=GROUPBY(B2:B11,A2:A11,LAMBDA(x,ROWS(UNIQUE(x))),,0)
Dynamic array formulas.

 

[blader1989]

Thank you @Sanjeev1976 and @shaowu459. Thank you so much for your solution. Both are work well with my desired!!!

Cheers!!!!

 

[shaowu459]

Thank you @Sanjeev1976 and @shaowu459. Thank you so much for your solution. Both are work well with my desired!!!

Cheers!!!!

thanks for your feedback, have a nice day