CODEWITHSUNDEEP
excel-formula
bd528
bd528

Reputation: 886

Counting instances between 2 dates

I have the following date ranges :-

01/01/2017 31/12/2017
01/06/2017 31/05/2018
01/02/2017 31/01/2018
01/01/2017 31/12/2017
01/06/2017 31/05/2018
01/02/2017 31/01/2018

Is it possible, via formula, to get a count of the ranges that include a specific date?

For example, a date of 31/01/2017 would give a result of 2 (the first and forth rows)

Upvotes: 0

Views: 65

Answers (1)

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19857

Use COUNTIFS:

The COUNTIFS function applies criteria to cells across multiple ranges and counts the number of times all criteria are met.
https://support.office.com/en-gb/article/COUNTIFS-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842

So with your data in the range A1:B6 and your criteria date in cell D1:
=COUNTIFS($A$1:$A$6,"<=" & $D$1, $B$1:$B$6,">=" & $D$1)

NB: COUNTIFS is available from Excel 2007 onwards.

For Excel 2003 use:
=SUMPRODUCT(($A$1:$A$6<=$D$1)*($B$1:$B$6>=$D$1))

Upvotes: 2

Related Questions