Google Sheets – Count Days Present in Overlapping Ranges

google sheets

Let's say I have some arbitrary number of date ranges in columns A and B (which can overlap), say:

     A         B
1    27.02.19  15.03.19
2    03.03.19  05.03.19
3    01.04.19  05.04.19

What I want to do is given a specific date, say 04.03.19, count how many times that date was contained in the ranges. In this case, the answer would be 2, because that date was contained within the ranges in rows 1 and 2.

What formula can I use in Google Spreadsheets to calculate that? I realize COUNTIF is one part of this puzzle, but I need to 1) check all ranges in multiple rows and 2) check if the date is within the range, and I'm not sure how to do that.

Best Answer

For my solution you will need another column, based on the next formula:

E1 = 04.03.2019 # The Date you want to find

=If(AND($E$1>=A2;$E$1<=B2);True;False)

Now we count

=COUNTIF("C:C";True) # You can use "C2:C200" if that syntax is not allowed

I have done it this way because, you can check if the date is in the range compounded by two columns(Assuming the index column doesn´t exist, if it does the shift everything one column to the right) then you count the results.

I find really hard to make a oneliner out of this because you need a range in countif or countifs and most formulas return a value.

Related Topic