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.

Find more posts tagged with

Accepted answers

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

All comments

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.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.