Counting Active projects within 2 dates

markkrebs
markkrebs ✭✭✭✭✭✭
edited 12/09/19 in Formulas and Functions

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

Tags:

Comments

  • Paul Newcome
    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)))

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • markkrebs
    markkrebs ✭✭✭✭✭✭

    Thanks Paul!  I'll give it a try.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help! Let me know how it goes...

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!