this formula appears to work but it looks to be stopping after the first @cell

Assistance is required please - all projects are cancelled, so it should report 0, it reports 2, like they are not cancelled

=IFERROR(IF(ISBLANK([Primary Column]@row), "", (COUNT(DISTINCT(COLLECT({2025 PMO Tracker V2 |Series}, {2025 PMO Tracker V2| PMO}, [Primary Column]@row, {2025 PMO Tracker V2| NPD-EXT}, "NPD-Category Expansion", {2025 PMO Tracker V2| PMO Status}, OR(@cell <>"Pending", @cell <> "Cancelled", @cell <> "Pending"), {2025 PMO Tracker v2 | YEAR}, $[Primary Column]$1))))), "error")

Find more posts tagged with

Accepted answers

JAAS

I changed the "OR" to an "AND" and it worked, backwards logic on my behalf, thank all .

All comments

Carson Penticuff

Without digging into it too deeply, the first thing I see is:

OR(@cell <> "Pending", @cell <> "Cancelled", @cell <> "Pending")

Pending is listed two times, should the last one be something, such as "Completed"?

JAAS

https://community.smartsheet.com/discussion/comment/473014#Comment_473014

Thank you. That was an example only; my apologies; I replaced the second pending with "on hold," so the formula is the same, I just transposed incorrectly into the request.

=IFERROR(IF(ISBLANK([Primary Column]@row), "", (COUNT(DISTINCT(COLLECT({2025 PMO Tracker V2 |Series}, {2025 PMO Tracker V2| PMO}, [Primary Column]@row, {2025 PMO Tracker V2| NPD-EXT}, "NPD-Category Expansion", {2025 PMO Tracker V2| PMO Status}, OR(@cell <>"Pending", @cell <> "Cancelled", @cell <> "On Hold"), {2025 PMO Tracker v2 | YEAR}, $[Primary Column]$1))))), "error")

JAAS

I changed the "OR" to an "AND" and it worked, backwards logic on my behalf, thank all .

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.