Create a dashboard widget to count project status by quarter.

Options

Hello everyone. I am a project manager using Smartsheet for the first time in a new organization.

I would like to create a widget on our PMO dashboard to display the amount of "New" and "Closed" projects in our system by quarter. Our data is pulled into an intake sheet via a form.

My approach is to pull data into a separate metrics sheet based on a date range that falls within each quarter. I attempted the following formula for the first quarter with no result:

"=COUNTIF({PM Intake Form Range 4}, >=DATE(2022,1,1), {PM Intake Form Range 4}, <=DATE(2022,3,31)"

"Range 4" contains the project start dates.

How can I adjust this formula to pull the data I'm looking for?

Thank you.

Best Answer

  • Sameer Karkhanis
    Sameer Karkhanis ✭✭✭✭✭✭
    Answer ✓
    Options

    For New projects (assuming your column name is Status)

    =COUNTIFS({PM Intake Form Range 4}, AND(@cell >=DATE(2022,1,1),@cell<=DATE(2022,3,31)), Status,"New")

    For Closed projects (assuming your column name is Status)

    =COUNTIFS({PM Intake Form Range 4}, AND(@cell >=DATE(2022,1,1),@cell<=DATE(2022,3,31)), Status,"Closed")

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!