Count Distinct Formula

Hello,

I have a sheet with over 200 cells of duplicate reference numbers and would like to only pull unique values from the column Req # and only if they are open reqs.

I am trying to create a simplified sheet that references/pulls in list of unique "open" Req Numbers

=IFERROR(DISTINCT(COLLECT({Dump: OPEN AND CLOSED REQS Range Req #}), " ")) but it is pulling as an incorrect argument.

I am pretty new to smartsheet. Thank you for all of your help :)

Find more posts tagged with

Accepted answers

All comments

roxxtheboxx

I have also tried doing something like this where I try to get it to only return if the req # is open

=INDEX(DISTINCT(COLLECT({Dump: OPEN AND CLOSED REQS Range Req #}, {Dump: OPEN AND CLOSED REQS Range Req #} {Dump: OPEN AND CLOSED REQS Range 2}, "Open")))

Paul Newcome

You would need a COUNT/DISTINCT/COLLECT.

=COUNT(DISTINCT(COLLECT({Range To Count Distinct Values In}, {First Criteria Range}, first_criteria, {Second Criteria Range}, second_criteria, …………………)))

roxxtheboxx

How would I be able to format this I am having issues creating the formatting for this

Paul Newcome

It would be formatted the way it is formatted in my above example.

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.