# Count of distinct (unique) values by day

1. ## Count of distinct (unique) values by day

Hi all,

Thanks in advance for any assistance you can render. I've been wrestling with this issue for the last hour, and haven't made much headway.

I've got a spreadsheet in the following format:

Date / Name / Date / Unique Names
Jan1 / Sue / Jan1 / 2
Jan1 / Bob / Jan2 / 1
Jan1 / Sue / Jan3 / 3
Jan2 / George
Jan2 / George
Jan3 / Bob
Jan3 / George
Jan3 / George
Jan3 / Sue

I want to automatically perform the count for the number of unique names on a given day, as I'm dealing with roughly 6000 entries over the past year. In other words, I'd like to count how many unique (distinct) names appear on January 1, on January 2, on January 3, and so forth.

Again, many thanks in advance for any assistance y'all can offer!

2. ## Re: Count of distinct (unique) values by day

Hi

One way would be to create your own function.

``Please Login or Register  to view this content.``
Assuming that your data is in the range A1:B9, the unique dates are in the range C1:C3, then enter the formula
``Please Login or Register  to view this content.``
in D1 and copy down to D3.

HTH

rylo

3. ## Re: Count of distinct (unique) values by day

Try this Array Formula, (with CTRL+SHIFT+ENTER, rather than just ENTER)

D2, copy down.

=SUM(SIGN(FREQUENCY(IFERROR(IF(A\$2:A\$10=C2,MATCH(B\$2:B\$10,B\$2:B\$10,0)),""),ROW(A\$2:A\$10)-ROW(A\$2)+1)))

4. ## Re: Count of distinct (unique) values by day

Those both worked - thanks so much, guys!

Velorian

5. ## Re: Count of distinct (unique) values by day

Guys, I have a similar issue, but easier.... I tried the formula provided, trying to modify it for my needs, but I'm missing something. What I need to do is get a count for Column A and Column B, ignoring the duplicates in Column A.

Column A Column B
JC Penny Mike
JC Penny David
Roy Rogers Walter
Roy Rogers Sarah
Roy Rogers Amy

Leads (Column B) = 9 (Already have this figured out, using a simple CountA)
Companies (Column A) = 3 (This is the bugger1!)

6. ## Re: Count of distinct (unique) values by day

Guys, I have a similar issue, but easier.... I tried the formula provided, trying to modify it for my needs, but I'm missing something. What I need to do is get a count for Column A and Column B, ignoring the duplicates in Column A.

Column A Column B
JC Penny Mike
JC Penny David
Roy Rogers Walter
Roy Rogers Sarah
Roy Rogers Amy

Leads (Column B) = 9 (Already have this figured out, using a simple CountA)
Companies (Column A) = 3 (This is the bugger1!)

7. ## Re: Count of distinct (unique) values by day

Hello SeanKosmann,

2. Don't post a question in the thread of another member -- start your own. If you feel it's particularly relevant, provide a link to the other thread.

8. ## Re: Count of distinct (unique) values by day

My apologies, been awhile since I've been on here, I'll create a new. Thanks!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1