COUNT(DISTINCT) to find only number of instances by date

RolandoN

Hi All,

I'm not sure where I found the formula, but using =COUNT(DISTINCT({SmartCount Email Validation Range 3})) gives me the total number of distinct retailers in a column called 'Retailers', which in my case is 35, which is correct.

I'd like to expand on that and only find those distinct number of retailers on a specific date. Using this gives me a totally different/unexpected number =COUNT(DISTINCT({SmartCount Email Validation Range 2}), {SmartCount Email Validation Range 1}, "10/03"), which comes out to 435. Correct answer should be 2.

Any ideas on what I can try and how? I tried using ISDATE after the first criteria, that didn't work.

Carson Penticuff

So you are going to utilize this formula on multiple rows, and each row will represent a different date? If that is the case, you could add a new "date" column (if you don't have one already) and reference that instead of hard coding the date into each formula. If you add a column named "Date", format it as DATE, and then list your relevant dates, you can use this instead:

=COUNT(DISTINCT(COLLECT({SmartCount Email Validation Range 2}, {SmartCount Email Validation Range 14}, Date@row)))

When you start dragging formulas from row-to-row, weird things can happen. Did you verify nothing besides the date changed when dragging the previous formula?