Unique Value In Range Of Time

ibmy

Board Regular
Joined
Mar 4, 2020
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
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
Joined
Jul 30, 2020
Messages
197
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Mar 4, 2020
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
.
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
Joined
Mar 4, 2020
Messages
63
Office Version
  1. 2016
Platform
  1. Windows
.
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.
.
 
Solution

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top