Counting Distinct Items in Sheet1 from Sheet2

I have a counting formula that's working in Sheet1. When I try to use that same formula in Sheet2 to pull the data from Sheet1, I get an UNPARSABLE error. What am I doing wrong?

Original formula (in Sheet1):

=COUNT(DISTINCT(COLLECT([Department]:[Department], [StartDate]:[StartDate], >=DATE(2025, 7, 1), [Status]:[Status], "completed")))

2nd formula (in Sheet2):

=COUNT({Sheet1Range 2}, (DISTINCT(COLLECT([Departmetn]:[Department], [StartDate]:[StartDate],>=DATE(2025, 7, 10), [Status]:[Status], "completed"))))

thank you!

Find more posts tagged with

Formulas

Accepted answers

Katherine Weybright

yes, the formula in Sheet2 is supposed to be counting from Sheet1, thanks Paul. I updated my formula in Sheet2 and it's now working. Thank you Paul!

=COUNT(DISTINCT(COLLECT({Sheet1Dept}, {Sheet1_StartDate}, >=DATE(2025, 7, 1), {*Sheet1__Status}, "completed")))

All comments

Paul Newcome

Is the formula in sheet 2 supposed to be counting fro sheet 1? If so, you would need a {Cross Sheet Reference} for each range within the COLLECT function. The syntax would be the same as the first but instead of [Column Name]:[Column name], You would have {Range Name}.

Katherine Weybright

yes, the formula in Sheet2 is supposed to be counting from Sheet1, thanks Paul. I updated my formula in Sheet2 and it's now working. Thank you Paul!

=COUNT(DISTINCT(COLLECT({Sheet1Dept}, {Sheet1_StartDate}, >=DATE(2025, 7, 1), {*Sheet1__Status}, "completed")))

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.