Create a dashboard widget to count project status by quarter.

Jonathan Feuer

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.

Sameer Karkhanis

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")

Sameer Karkhanis

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")

Jonathan Feuer

This solved my problem. Thank you @Sameer Karkhanis.