count the number of unique records within date range

I have a spreadsheet with the following three columns (A=resource code, B=start date, C=end date). I am trying to count the number of unique resource codes that overlap for any date of the year.

A B C
Resource Code Start Date End Date
JF 23-Dec-21 07-Jan-22
ED 01-Nov-21 31-Apr-22
ED 01-Apr-22 31-Jun-22
ED 13-Sep-21 31-Mar-22
EN 03-Jan-22 07-Jan-22
ED 11-Jan-22 21-Mar-22
JF 17-Jan-22 01-Feb-22
CHST 17-Jan-22 31-Mar-22
FROL 17-Jan-22 31-Mar-22
THBR 17-Jan-22 31-Mar-22
ED 01-Jan-22 31-Mar-22

If A6 = 20/04/22

The code I have got so far is:

``````=COUNTIFS(\$B\$2:\$B\$1000,"<="&A6,\$C\$2:\$C\$1000,">="&A6)
``````

This comes up with the count of rows with date ranges that the date 20/04/22 falls within (i.e. twice). However I want to only count this when there are unique records. In this instance it is ED that is the resource code for both instances. This is only one unique resource code, and therefore I would like my formula to return '1'. I have had a play with unique() function but have not succeeded.

• Welcome! I won't answer your question yet, but I want to draw your attention to lines 3 and 4. There are only 30 days in April and June. So when you write `31-Apr-22` and `31-Jun-22`, the tables think that you are talking about `22-Apr-1931` and `22-Jun-1931`. Of course, even the most correct formula will not give the correct result with such data. Aug 31, 2022 at 15:50
• Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
– Community Bot
Sep 6, 2022 at 2:26

To get the number of unique values, you can first use a filter to select the desired rows by condition

`=FILTER(A2:A, B2:B <= \$F\$6, C2:C > \$F\$6+1)`

(You wrote that the date being checked is in A6 - I think this is a typo and you meant F6, since EN is in A6)

Then apply the UNIQUE () function to the resulting selection.

`=UNIQUE(FILTER(A2:A, B2:B <= \$F\$6, C2:C > \$F\$6+1))`

A normal, non-conditional COUNTA() will return the number, and - if necessary - TEXTJOIN() will show a list of resource codes selected for counting:

`=COUNTA(UNIQUE(FILTER(A2:A, B2:B <= \$F\$6, C2:C > \$F\$6+1)))`

`=TEXTJOIN(",",1,UNIQUE(FILTER(A2:A, B2:B <= \$F\$6, C2:C > \$F\$6+1)))`

One more note about the condition `C2:C > \$F\$6+1`. It is recommended to check the end date of the period in this way. If your data suddenly encounters not a pure date, but a date-time, then this row may be lost for calculation.