count index collect returning 1

Hello all,

Need your assistance. I read some of the similar issues here in the community but still I can't figure out what I did wrong.

I have this formula to get the unique serial number of specific product offering. The formula works fine. But if I add a date range, it returns 1. In some product offering, it also returns 1 but it should be zero (0). I tried creating helper column for serial number, but still returns 1.

Formula without date range: =COUNT(DISTINCT(COLLECT({Serial Number}, {Product Offering}, ="DT")))

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

My goal is to count the unique SN of specific product offering in the last two weeks (Monday to Friday).

Appreciate if someone could help me out, thank you in advance. 😉

Best Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Based on your screenshot, your formula should be outputting zero. Are you able to provide a screenshot that shows more than one row that should be getting counted based on the criteria in your formula?

    Also try removing the DATE functions.

    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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    AND(@cell >= TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0) - 7, @cell <= TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0) - 3)

    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

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    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

  • RaffyM
    RaffyM ✭✭✭✭✭

    Hi @Paul Newcome thank you for your response. I created this another thread because after posting my initial inquiry in that thread, I can not find that discussion. I thought it was not posted.

    Anyway, regarding your request, here is the screenshot of the sheet. I hope this fine to understand more of what is my missing.

    I would like to count the unique serial number when product offering is MTS, D2 or DT in the last week or two (Monday to Friday). Take note that in the screenshot above, it doesn't show other product offerings and other unique serial numbers because the list is to big because of duplicate serial numbers.

    Again, using this formula I was able to get the total count.

    =COUNT(DISTINCT(COLLECT({Serial Number}, {Product Offering}, CONTAINS("MTS", @cell))))

    But when I added another range (date range), it returns 1 only.

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

    Same for D2 and DT, it returns 1 only. Returning 1, does it mean my formula is incorrect?

    Thank you again

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Based on your screenshot, your formula should be outputting zero. Are you able to provide a screenshot that shows more than one row that should be getting counted based on the criteria in your formula?

    Also try removing the DATE functions.

    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

  • RaffyM
    RaffyM ✭✭✭✭✭

    I should thought and tried of removing the DATE function. Thanks @Paul Newcome. It's always better to have second pair of eyes.. 😀

    I removed DATE functions and it gives correct result for D2 and DT but not MTS, still returning 1 instead of 3. Screenshot below of 3 different unique SN.

    Also because of my formula, it won't always count Monday to Friday data. If I am checking on Tuesday, then the data capture is between Tuesday to Saturday. Could you suggest what function I should use instead?

    =COUNT(DISTINCT(COLLECT({Serial Number}, {Product Offering}, CONTAINS("MTS", @cell), AND(@cell >= TODAY(-14), @cell <= TODAY(-3)))))

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Your most recent formula doesn't have a {Date Range}.

    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

  • RaffyM
    RaffyM ✭✭✭✭✭

    Oh yeah, probably I accidentally delete because the other product offering has.

    The formula is working now, however my next concern is what function I should use to replace this AND(@cell >= TODAY(-7), @cell <= TODAY(-3) because it will always have different days covered, depending on when the formula ran. My goal is to get the unique SN of previous week Monday to Friday.

    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Try this:

    AND(@cell >= TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0) - 7, @cell <= TODAY() + (2 - WEEKDAY(TODAY())) - IF(WEEKDAY(TODAY()) < 2, 7, 0) - 3)

    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

  • RaffyM
    RaffyM ✭✭✭✭✭

    Thank you @Paul Newcome. It works, and give the same results as my earlier formula maybe because my raw data doesn't much have info from last week. I will check and confirm again tomorrow if output remains the same.

    Have a good one the rest of the day!

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Happy to help. 👍️

    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!