Count date occurrences between date periods within 2 separate columns (start and end date)

Hi,
I am new to this and will try to make it simple as possible and would really appreciate it if someone could help me please

I have 4 columns:

Column A Column B Column C Column D (Currently blank and column I want to fill).
Date Start Date End Date Active Days


My aim is to use the Date in column A against columns B and C and get the count of the total number of times the date in column A is within the date ranges of column B and C.

Capture.PNG

As shown from the attached image and the attached spreadsheet, I am looking to populate column D with the amount of times a given date from column A occurs within the date ranges of columns B and C and populate the total count in column D.

The first 2 cells have been manually entered as an example where for 11/11/2019, the answer should be 2 as it is between 2 date ranges within column 2 and 3.

I have tried to use a countifs function by using: =COUNTIFS(B2:C13,">="&A2,B2:C380,"<="&A2), however for date 11/11/2019 it incorrectly returns a count of 3 instead of 2.

Could someone please help me? I am willing to clarify anything as I am sure this does not make sense!

Thanks!

try this sumproduct in D2 and drag down.

=SUMPRODUCT(($B$2:$B$15<=$A2)*($C$2:$C$15>=$A2))

you may need to change your last row which i indicated in red if you have more rows than what you showed in the picture

Thank you! This has resolved my issues!!! I will add a reputation for you

Hi,

If you get the chance could you please give me a quick breakdown of what the formula you have provided is exactly doing? I just want to make sure I don't just copy what you done but also learn it myself for future reference!

Thank you for the reputation, and im glad it worked.
Quick explanation of what is happening in formula:

It is creating a Matrix based on 2 arrays. Think of this like a table with 2 columns, where the amount of rows is based on the amount of Data in your column B and C. The first column is based on logic ($B$2:$B$15<=$A3) while the second column is based on logic ($C$2:$C$15>=$A3).
Lets use your date in A3 as an example November 12th 2019. It takes this date and compares it to B2 first. Because 11/12/19 is greater than 11/11/19 which is in B2 it puts a 1 in column one row one of your Matrix as seen below. It then compares A3 to B3. This has the same result so it puts a 1 in column one row two of your matrix. It continues this down column B. If the logic is False, then it returns a 0. so using your data for cell A3 the matrix for your first logic looks like {1,1,1,1,1,0,0,0,0,0,0,0} It then compares A3 to column C logic. Again return 1 if true, and 0 if False. the result looks like {1,0,1,1,1,1,1,1,1,1,1,1}. I put a Multiplication sign between the two arrays in the original formula so now your formula results look like this:
{1,1,1,1,1,0,0,0,0,0,0,0} *{1,0,1,1,1,1,1,1,1,1,1,1}

This returns the values of {1,0,1,1,1,0,0,0,0,0,0,0}. Which when you sum up = 4.