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.
C1=04.03.19
, then=COUNTIFS(A1:A3,"<="&C1,B1:B3,">="&C1)
– Akina Mar 12 '19 at 12:56