count the number of unique records within date range

Question

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.

Answer 1

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.