COUNT DISTINCT COLLECT Formula help

This discussion was created from comments split from: Count Distinct Collect Returning 1.

Answers

  • RaffyM
    RaffyM ✭✭✭✭✭

    Hello @Genevieve P.

    I have a formula similar to above but it returns 1 when I added the date range. Could you help me check what is wrong?

    Without date range, this is my formula and it counts the correct distinct value.

    =COUNT(DISTINCT(COLLECT({Serial Number}, {Product Offering}, ="DT")))

    Here is the formula with date range. My goal is to get the distinct SN in the last two weeks.

    =COUNT(DISTINCT(COLLECT({Serial Number}, {Product Offering}, ="DT", {Audit Date}, AND(@cell >= DATE(TODAY(-14)), @cell <= DATE(TODAY(-3))))))

    Also, I have other Product Offering (D2 or C3) but it returns 1 even if there is no distinct serial number in the last two weeks? I'm expecting to get 0 result so it won't show as data when I make a chart in the dashboard

    Thank you for the assistance always. 😉

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @RaffyM Are you able to provide some screenshots for context to include rows that are getting counted but shouldn't and rows that are not getting counted but should?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!