count index collect returning 1

RaffyM

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

Paul Newcome

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.

Paul Newcome

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)

Paul Newcome

@RaffyM I responded on your other thread:

https://community.smartsheet.com/discussion/comment/437841#Comment_437841

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

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.

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

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

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

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)

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

Happy to help. 👍️