How to count projects by status and date range

Hi, I'm trying to count the number projects based 2 criteria: status and date range. Both of criteria are located in separate raw sheet I have been getting #unparseable #invalid operation. Below is sample formula used, basically I'm trying to count projects in year 2020 that have "status" complete:

COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , AND ({Integration 2} >=DATE(2020, 01, 01), <=DATE(2020, 12, 31))).

Can anyone help? Thank you

Find more posts tagged with

Accepted answers

Febriandini Bourikas

Hi @Paul Newcome , thank you very much, I had to update the reference sheet but it worked!

Now that I'm succeeded calculating number of projects in a certain year, do you know how can I calculate within a certain month of the year?

All comments

Paul Newcome

Try something like this...

=COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , {Integration 2}, IFERROR(YEAR(@cell), 0) = 2020)

Febriandini Bourikas

Hi @Paul Newcome , thank you very much, I had to update the reference sheet but it worked!

Now that I'm succeeded calculating number of projects in a certain year, do you know how can I calculate within a certain month of the year?

Paul Newcome

Yes. You would use an AND statement to reference that same range and then use a MONTH function the same way we used the YEAR funciton.

=COUNTIFS({Integration Projects: Raw Data Sheet Range 1}, "Complete" , {Integration 2}, AND(IFERROR(YEAR(@cell), 0) = 2020, IFERROR(YEAR(@cell), 0) = 7))

Febriandini Bourikas

Thank you @Paul Newcome!

Paul Newcome

Happy to help! 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.