COUNT DISTINCT COLLECT Formula help

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

Answers

  • RaffyM
    RaffyM ✭✭✭✭✭
    Options

    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 ✭✭✭✭✭✭
    Options

    @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?

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!