Count Distinct Values

I am a little stumped here. I have a sheet with several columns. I need to count the distinct # of SKU's with the same Status Action, the same Implementation Name, but from different Distribution Centers.

The formula I am using is the =COUNT(DISTINCT(COLLECT....)

I have the formula on a different sheet since I am counting multiple sheets from different people.

=COUNT(DISTINCT(COLLECT({DOREEN_Implementation Active_Project Phoen Range 3}, {DOREEN_Implementation Active_Project Phoen Range 2}, "NIR In Progress", {DOREEN_Implementation Active_Project Phoen Range 1}, "JUN_2021_PP_CEREAL", {DOREEN_Implementation Active_Project Phoen Range 6}, "BWC Sysco Idaho, Inc.")))

I can count it using just 1 Distribution Center. But I need to use all of my Distribution Centers. Please see snips below. I really do not want to use a helper column since I already have so many columns and there are thousands of rows on each sheet.

I can keep repeating the formula and add all the counts but other sheets have 10-15 different distribution centers.

Find more posts tagged with

Accepted answers

All comments

Bassam Khalil

Hi @Doreen Chatfield

Hope you are fine, please try the following formula:

=COUNT(DISTINCT(COLLECT({New Distrb Sku Nbr Range}, {Distribution Center Range}, "BWC Sysco Idaho, Inc.", {Status Action Range}, "NIR In Progress", {Implementation Name Range}, "JUN_2021_PP_CEREAL")))

the following screenshots shows the result and the reference searching range:

the searching range:

Doreen Chatfield

Hi Bassam,

Thank you for the quick reply.

I can do that and it would count only for the Idaho items. I need it to include all of the distribution centers.

So I need a formula that would also count the distinct distribution centers after it counts the distinct skus.

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.