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!


Best Answer

  • Christian G.
    Christian G. ✭✭✭✭✭✭
    edited 11/04/22 Answer ✓

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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!