Need help to write a formula to count projects within a date range for the specific project manager

Happy New Year. I am trying to write a formula that allows me to count the # of projects with in a date range i.e. greater than 4/1/20 and less than Today's date for a specified project manager


  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    edited 01/07/21


    Without knowing, I assumed you were doing this between two sheets and that your Project Mgr names were listed in a column on your destination sheet. This is counting all projects assigned to any specific project manager, not projects of a certain status, ie "completed".

    =COUNTIFS({source sheet project manager}, Name@row = @cell, {source sheet Start Date}, @cell >= DATE(2020, 4, 1), {source sheet End Date}, @cell <= TODAY())

    If it wasn't between two sheets and you need help getting the formula for the single sheet, call out and I'll be happy to convert this. You will have to edit the formula for your column names and your specific cross referenced ranges.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!