Counting Unique Data Only if Associated Column is Not Blank

Hello again! We really do research these things before we ask! ;)

We are looking to count the unique Store Numbers for "P1 Complete Dates" and "P2 Complete Dates" in the Sheet's Summary Data, so we can make sure each store is only being counted once for each phase.

The formula that we found and tried is only giving a result of 1, which we know is inaccurate.

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date]), <>""))

Test sheet: https://app.smartsheet.com/b/publish?EQBCT=a3686cd8c2ff4bcc8047b4ba54bc30ce

Thanks for taking a look!

Find more posts tagged with

Accepted answers

Kelly Moore

@Nancy Heater

Try this

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))

You had a parenthesis too early, which closed off the last criteria too soon.

All comments

Kelly Moore

@Nancy Heater

Try this

=COUNT(DISTINCT(COLLECT([Store Helper]:[Store Helper], [P1 Complete Date]:[P1 Complete Date], <>"")))

You had a parenthesis too early, which closed off the last criteria too soon.

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.