Count unique values in date range

**Shawn1888** · 04-30-2018, 12:23 PM

I'm trying to count the number of unique values in a date range. My dates are in Column I, and I have a reference number (which is what I'm basing uniqueness on) in column O. For this project, I'm going to have enough data that using the entire column needs to be used.

To determine the count of unique values, I've been using the following formula:

=SUM(IF(FREQUENCY(O:O,O:O)>0,1))

To determine the total number of values in a date range, I've been using the following formula:

=COUNTIFS(I:I,">="&DATE(2017,3,1),I:I,"<="&DATE(2017,3,31))

Any help would be greatly appreciated!

**JeteMc** · 05-05-2018, 09:53 AM

This proposed solution employs a helper column (K) which may be moved and/or hidden for aesthetic purposes.
The helper column is populated using: =IF(AND(I2>=L$1,I2<=M$1),J2,"")
Note that cells L1 and M1 contain the start and end dates of the range.
The formula for counting unique values is: =SUM(IF(FREQUENCY(K2:K35,K2:K35)>0,1))
Test by changing the date in L1 to 2/28/2017 and note that the three is counted.
Let us know if you have any questions.