Unique Value In Range Of Time

[ibmy]

Hi,

Example of my 200k row data:

6 2 20 d.xlsb
	A	B	C	D	E
1	Time	A
2	1:00:00.000	1.5		2:00	5
3	1:30:00.000	1.5
4	2:00:00.000	1.2
5	2:01:00.000	1.2
6	2:45:00.000	1.3
7	2:50:00.000	1.3
8	2:55:00.000	1.5
9	2:59:00.000	1.5
10	2:59:08.000	1.9
11	2:59:59.000	1.9
12	2:59:59.020	1.4
13	3:00:00.000	1.1
14	22:00:00.000	1.4
Sheet5

A Column : Time
B Column : Value
D Column : Range of Time
E Column : Expected Unique Value

Example above , the range of time is from 2:00 to 3:00 ( >=2:00, <3:00 )

Currently use =SUM(IF(FREQUENCY(B2:B200000, B2:B200000)>0,1)) to count unique value of whole data without range of time criteria.
=SUMPRODUCT(1/COUNTIF(B2:B200000,B2:B200000)) is not working on my data.

Was thinking to use single cell reference range of time like ( ">="&D2,$A$2:$A$200000,"<"&TIME(HOUR(D2)+1,MINUTE(D2),0 ) but if I need 2 cell reference for start and end of time is acceptable or inside the formula state of start and end of time.

I search on google and past mrexcel thread, most of the solution are in range of date.
How To Count Unique Values Between Two Dates In Excel?
Formula to count unique Values in a date range needed.
Countif unique value within date range
Adding date range to FREQUENCY function
need a count of unique dates in same row and specific date range
Count Distinct Values within a date range
Count unique text values between dates
Count Unique values between two dates
VBA - Count Unique String Values Between Dates

 

[Habtest]

Hi, try:

Book1
	A	B	C	D	E
1	Time	A
2	1:00:00	1.5		2:00:00	5
3	1:30:00	1.5
4	2:00:00	1.2
5	2:01:00	1.2
6	2:45:00	1.3
7	2:50:00	1.3
8	2:55:00	1.5
9	2:59:00	1.5
10	2:59:08	1.9
11	2:59:59	1.9
12	2:59:59	1.4
13	3:00:00	1.1
14	22:00:00	1.4
Sheet1

Cell Formulas
Range	Formula
E2	E2	=SUMPRODUCT(IF((A2:A14>=D2)*(A2:A14<D2+1/24),1/COUNTIFS(A2:A14,">="&D2,A2:A14,"<"&D2+1/24,B2:B14,B2:B14),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[ibmy]

.
Not working on 100-200k row data, for small size of data it is working.

Currently use =SUM(IF(FREQUENCY(B2:B200000, B2:B200000)>0,1)) to count unique value of whole data without range of time criteria.
=SUMPRODUCT(1/COUNTIF(B2:B200000,B2:B200000)) is not working on my data.

I think frequency function would work.

Real data : 120k row
.

 

[ibmy]

.
Update: I found the solution ? after 3 days search on the net. It is hard for me and very satisfy at the end since I have a little basic knowledge of Excel.

Counting unique values within date range

I play around with the formula, changing here and there and came up with :

=SUM(IF(FREQUENCY(IF((D2<=$A$2:$A$200000)*( D2+1/24>$A$2:$A$200000),$B$2:$B$200000,""),IF((D2<=$A$2:$A$200000)*(D2+1/24>$A$2:$A$200000),$B$2:$B$200000,""))>0,1))

Thanks for everyone viewing my thread.
.