Formula question - count projects by status, category and date

Hi Everyone,

I want to count the number of Project IDs where the Project Status = "Complete", the Project Category matches the column header (AV-TV, Software, etc.) and the End Date is greater than 2022,09,30. I can count the number of overall complete projects for the project category without the date, but cannot seem to get the date to work also.

=COUNT(COLLECT({Project ID}, {Project Status3}, $Label@row, {Project Category}, [AV-TV]$1))

I've tried many different combinations with this as the last. Although it is no longer UNPARSABLE it does not count correctly.

=COUNT(AND(COLLECT({Project ID}, {Project Status}, $Label@row, {Project Category}, [AV-TV]$1), {End Date} > DATE(2022, 9, 30)))

Can anyone offer a solution?

Thank you for any help!

Find more posts tagged with

Formulas

Accepted answers

Christian G.

You dont need the AND()

Simply add more contition to the Collect

=COUNT(COLLECT({Project ID}, {Project Status}, $Label@row, {Project Category}, [AV-TV]$1,{End Date},> DATE(2022, 9, 30)))

All comments

Christian G.

You dont need the AND()

Simply add more contition to the Collect

=COUNT(COLLECT({Project ID}, {Project Status}, $Label@row, {Project Category}, [AV-TV]$1,{End Date},> DATE(2022, 9, 30)))

wdouglass

Christian,

This works perfectly! Thank you very much.

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.