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?

Carson Penticuff

Try this:

=COUNT(DISTINCT(COLLECT({SmartCount Email Validation Range 2}, {SmartCount Email Validation Range 1}, DATE(2023, 10, 03))))

RolandoN

Thanks Carson, I tried this =COUNT(DISTINCT(COLLECT({SmartCount Email Validation Range 2}, {SmartCount Email Validation Range 14}, DATE(2023, 10, 3)))) and it worked for the same row. I got a result of 2, which is correct.

But when I dragged formula to the second row and changed the date to 2023, 10, 04, it gave me a value of 6 instead of 8. So close. What else could I do?

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?

RolandoN

Hi Carson,

Yes each row will represent a different date. I used your new formula, but got a total of zero. Here's what my sheet looks like. The numbers from 10/4 and below I got from manually selecting the cells to distinct from, which are correct numbers. Just trying to use a formula instead.

Carson Penticuff

Is it possible some of the dates in your source sheet are not formatted as text instead of dates?

RolandoN

Sorry I didn't notice that. It was a text column. But even when I use a different column that is a date column, I'm still getting different numbers. Let me try something else. I'm sure it's user error (me).

RolandoN

ok great, everything (my data) is cleaned up and the last formula you provided works! Thank you SO much!

Carson Penticuff

Awesome, I'm glad its working for you!