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.


Answers

  • Bassam Khalil
    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:


    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • 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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!