Count Distinct Values

Doreen Chatfield

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.

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.