1

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.

2
  • 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.
    – JohnSUN
    Aug 31 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 at 2:26

1 Answer 1

0

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.

1
  • This worked a treat. Thanks so much! Yes, I see that I made a couple of errors with length of month, and the cell for the search date. Well done for deciphering and helping regardless! Sep 12 at 16:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.