# unique values in an array

#### keresztesi

##### Board Regular
Hi,

I have the following table:

In column "A" : dates (dd.mm.yyyy)
In column "E": times (hh:mm)

I'd like to have the followings:
18:00 <= Times < 22:00

How many unique days match that criteria?

#### Domenic

##### MrExcel MVP
To return a count of unique times that are greater than or equal to 18:00 and less than or equal to 22:00, try the following formula that needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(E2:E100>="18:00:00"+0,IF(E2:E100<="22:00:00"+0,E2:E100)),E2:E100)>0,1))

or

=SUM(IF(FREQUENCY(IF(E2:E100>=G2,IF(E2:E100<=H2,E2:E100)),E2:E100)>0,1))

...where G2 contains the start time, and H2 contains the end time. To include the date, try the following formula instead that also needs to be confirmed with CONTROL+SHIFT+ENTER...

=SUM(IF(FREQUENCY(IF(A2:A100+E2:E100>="1/15/2017 18:00:00"+0,IF(A2:A100+E2:E100<="1/15/2017 22:00:00"+0,A2:A100+E2:E100)),A2:A100+E2:E100)>0,1))

or

=SUM(IF(FREQUENCY(IF(A2:A100+E2:E100>=G2+H2,IF(A2:A100+E2:E100<=G2+I2,A2:A100+E2:E100)),A2:A100+E2:E100)>0,1))

...where G2 contains the date, H2 contains the start time, and I2 contains the end time. Adjust the ranges accordingly. However, try to avoid using whole column references, since it would likely slow down the calculations considerably.

Hope this helps!

#### keresztesi

##### Board Regular
Hi Domenic,

I tried your 3rd formula, but it gives me "0" as result. Any other ideas?

Thx

#### keresztesi

##### Board Regular
Ok,

I figured it out. It works well with this formula:

=SUM(((FREQUENCY(IF('[bizerba2_du.xls]BEVITEL '!\$E10:\$E1000>=0,75;1;0)*IF('[bizerba2_du.xls]BEVITEL '!\$E10:\$E1000<0,916666666666667;1;0)*(COUNTIF('[bizerba2_du.xls]BEVITEL '!\$A10:\$A1000;">"&'[bizerba2_du.xls]BEVITEL '!\$A10:\$A1000)+1);ROW('[bizerba2_du.xls]BEVITEL '!\$A\$10:\$A\$1000)-ROW('[bizerba2_du.xls]BEVITEL '!\$A\$10)))*(ROW('[bizerba2_du.xls]BEVITEL '!\$A\$10:\$A\$1001)-ROW('[bizerba2_du.xls]BEVITEL '!\$A\$10))>0)*1)

BUT!

It works only when the external reference file is open!
Is there a way to use it without opening all the external files?

Thx

#### Domenic

##### MrExcel MVP
Hi Domenic,

I tried your 3rd formula, but it gives me "0" as result. Any other ideas?

Thx
Can you post about 10 rows of sample data, provide the expected results based on the posted data, and provide the exact formula that you tried?

1,082,278
Messages
5,364,201
Members
400,786
Latest member
ismi88

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...