Counting the matches of a date in time periods in Excel-CodePudding

I am trying to find a (reasonably elegant) formula to find out how often a date appears in a list of date ranges.

In my example I have 4 date ranges, defined by a start date (A2:A5) and by an end date (B2:B5). Below I have a list of dates for which I would like to know how often a date appears in any of those 4 ranges. The only solution I came up with was to check for each range if the date is in there. The formula becomes quite lengthy with the number of periods and is not flexible if more periods are added later.

Here the time periods:

And here where I try to retrieve the number of matches given a date:

My formula is here for B11 (yielding 3 given the input of 10/Sep/2021): =IF(AND($A11>=$A$2,$A11<=$B$2),1,0) IF(AND($A11>=$A$3,$A11<=$B$3),1,0) IF(AND($A11>=$A$4,$A11<=$B$4),1,0) IF(AND($A11>=$A$5,$A11<=$B$5),1,0)

Any ideas appreciated!

CodePudding user response：

Using COUNTIFS:

=COUNTIFS($A$2:$A$5,"<="&A11,$B$2:$B$5,">="&A11)