Counting Active projects within 2 dates

markkrebs

I have 2 date columns:  Actual Start and Actual Finish in my intake sheet and want to summarize active projects by month over time.  

I setup trend sheet with months and years and want the formula to count actives for the month if:

Actual Start is NOT blank

AND Actual Start (Less than or equal to the Month/Year on the row in the Trend sheet)

AND Actual Finish is blank OR > Month Year on the row in the trend sheet

Paul Newcome

If your trend sheet is set up as below (column headers in bold)...

.

MONTH           YEAR          COUNT

       1                2019                 f

       2                2019                 f

       3                2019                 f

       4                2019                 f

.

You will want to use something like this...

 

=COUNTIFS({Source Sheet Actual Start Date}, AND(MONTH(DATEONLY(@cell)) <= Month@row, YEAR(DATEONLY(@cell)) <= Year@row), {Source Sheet Actual Finish Date}, OR(ISBLANK(@cell), AND(MONTH(DATEONLY(@cell)) >= Month@row, YEAR(DATEONLY(@cell)) >= Year@row)))