# Unique Value In Range Of Time

#### ibmy

##### Board Regular
Hi,

Example of my 200k row data:
6 2 20 d.xlsb
ABCDE
1TimeA
21:00:00.0001.52:005
31:30:00.0001.5
42:00:00.0001.2
52:01:00.0001.2
62:45:00.0001.3
72:50:00.0001.3
82:55:00.0001.5
92:59:00.0001.5
102:59:08.0001.9
112:59:59.0001.9
122:59:59.0201.4
133:00:00.0001.1
1422:00:00.0001.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

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### Habtest

##### Board Regular
Hi, try:
Book1
ABCDE
1TimeA
21:00:001.52:00:005
31:30:001.5
42:00:001.2
52:01:001.2
62:45:001.3
72:50:001.3
82:55:001.5
92:59:001.5
102:59:081.9
112:59:591.9
122:59:591.4
133:00:001.1
1422:00:001.4
Sheet1
Cell Formulas
RangeFormula
E2E2=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

##### Board Regular
.
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
.

Last edited:

#### ibmy

##### Board Regular
.
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 :
Excel Formula:
``=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.
.

Replies
2
Views
183
Replies
29
Views
512
Replies
3
Views
253
Replies
2
Views
460
Replies
12
Views
205

1,147,734
Messages
5,742,862
Members
423,760
Latest member
photogfrog

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back