=COUNT(DISTINCT(COLLECT formula

Options

Hi,


I have a =COUNT(DISTINCT(COLLECT formula that is working in my sheet summary area. However when I try to recreate it in a metric sheet that pulls data from my sheet it only counts 1.


Here is the formula that is working in my sheet summary.

=COUNT(DISTINCT(COLLECT(Customer:Customer, [Who is your audience]:[Who is your audience], ="customer facing")))


Here is the formula that is NOT working in my metric sheet. It gives me a count of 1 when it should be 209.

=COUNT(DISTINCT(COLLECT({CX Global Presales Design Services Project Range 8}, {CX Global Presales Design Services Project Range 9} = "customer facing")))


Any idea what i am doing wrong on my metric sheet? Also how can i add a date range to the one being used on my metric sheet?


Thank you in advance for your time,

Tags:

Best Answers

Answers

  • shawna kalke
    Options

    Hi Genevieve,


    You are amazing. THANK YOU SO MUCH!!!!!! Both your correction and your date formula WORK!!!

    I have an issue with one more countifs formula. I am not sure why I can't work it out and it should be an easy one.

    I need to count three columns together , that does not include blanks , that are between dates, all from another sheet. Do you know how to achieve this?


    Shawna

  • shawna kalke
    Options

    Hi Again,


    I worked out a formula to do what i want for the second one. However is there a better what to write it?


    =COUNTIFS({Design Services Projec Range 12}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30)) + COUNTIFS({Design Services Projec Range 10}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30)) + COUNTIFS({Design Services Projec Range 13}, "<>", {Design Services Project Range 4}, >=DATE(2020, 7, 1), {Design Services Project Range 4}, <=DATE(2020, 9, 30))

    Also not sure why when i link the sheet it is now dropping the T on project.

  • Matt Stewart
    Options

    @Genevieve P.


    How can i do this without double counting?


    Ive got a summary metric sheet im trying to pull in the # of projects a person has with a stage gate due in next 30 days. We have different levels of assignment, that may or may not be the same person.


    For example, an Art Director may also be the Designer. So if a design is due within next 30 days, it needs to filter out the criteria as usual, but then it needs to count 1 if any of these scenarios are true:

    • the Primary Column person is selected in the {Art Director} range only
    • the Primary Column person is selected in the {Designer} range only
    • the Primary Column person is selected in the {Art Director} range OR the {Designer} range


    The first 2 scenarios are easy to do as they are separate ranges to check. But when i add these 2 scenarios to replicate scenario 3 it ends up double counting instead of counting as 1 because its the same distinct item.


    my existing formulas:

    Art Director:

    =COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Product Designer}, $[Primary Column]@row, {SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))

    Designer:

    =COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Sculpt Designer}, $[Primary Column]@row, {SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))

  • Genevieve P.
    Options

    Hi @Matt Stewart

    You would want to create a third formula that COUNTS any rows that have both. Then when you have that number, you'd subtract it from the total count of the Art Director column and Designer column added together... like so:

    =(COUNT(DISTINCT(COLLECT(Art Director Formula)))COUNT(DISTINCT(COLLECT(Designer Formula)))) - COUNT(DISTINCT(COLLECT(Both Together))) 


    So your third formula would be something like this:

    =COUNT(DISTINCT(COLLECT({SKU Detail - Sku}, {SKU Detail - Sculpt Designer}, $[Primary Column]@row, {SKU Detail - Product Designer}, $[Primary Column]@row,{SKU Detail - Delivery Month}, <>"", {SKU Detail - Parent/Children}, <>11, {SKU Detail - Uncertain}, <>1, {SKU Detail - Projected 3D Handoff}, <=(TODAY() + 30), {SKU Detail - Actual 3D Handoff}, OR(@cell = "", @cell >= TODAY()))))


    Let me know if this works for you!

    Cheers,

    Genevieve

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!