How can I use COUNTIFS to get a count of projects and filter by date?

Hi, I had created a formula to give me a count of completed projects, but then my manager needed a count within a financial year. I can't get the formula to work, so I'm wondering if dates are not usable with that formula. If that's the case, what should I used instead?

=COUNTIFS(([Project State]:[Project State], ="Completed", ([Planned Finish]:[Planned Finish], @cell >= DATE(2023, 02, 01), @cell <= DATE(2024, 01, 31)))

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

Dates can be used, but your syntax is a bit off. Try this instead:

=COUNTIFS([Project State]:[Project State], @cell="Completed", [Planned Finish]:[Planned Finish], AND(@cell >= DATE(2023, 02, 01), @cell <= DATE(2024, 01, 31)))

Colleen Jones

You are a genius! Thank you so much. That fixed it!!

Leroy Noriega

Hello,

I would create three helper columns, Month, Quarter and Year. I utilize the end date in my project plans:

Month - =MONTH([End Date]@row)

Quarter - =IF(OR(Month@row = 1, Month@row = 2, Month@row = 3), "Q1", IF(OR(Month@row = 4, Month@row = 5, Month@row = 6), "Q2", IF(OR(Month@row = 7, Month@row = 8, Month@row = 9), "Q3", IF(OR(Month@row = 10, Month@row = 11, Month@row = 12), "Q4", ""))))

Year - =YEAR([End Date]@row)

By doing this you have the information in the back end that you can utilize whenever needed. It makes it easier for reporting. You can then hide it so others don't see it.

You can now pull a report based on the appropriate status and year.

Colleen Jones

Thanks Leroy! I'll keep this in mind for when I'm not frantically trying to provide metrics to my manager. This sounds like another good way to do this.

Paul Newcome

@Leroy Noriega You don't need to pull out the month and year into separate columns. You can use the MONTH and YEAR functions inside of whatever other function you are using. An example to count for all dates in Jan 2024 would be

=COUNTIFS([Date Column]:[Date Column], AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2024))

You can also simplify your quarterly formula (assuming you are using calendar year quarters) like so:

="Q" + ROUNDUP(MONTH([Date Column]@row) / 3)

Leroy Noriega

Thank you! @Paul Newcome

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.