COUNT DISTINCT COLLECT Formula help

System

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

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

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