Create a dashboard widget to count project status by quarter.
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
-
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
-
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")
-
This solved my problem. Thank you @Sameer Karkhanis.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.4K Get Help
- 396 Global Discussions
- 213 Industry Talk
- 448 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 133 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 293 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!