Count of distinct (unique) values by day

**velorian** · 12-05-2011, 07:44 PM

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!

**rylo** · 12-05-2011, 08:36 PM

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

**Haseeb Avarakkan** · 12-05-2011, 09:03 PM

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)))

**velorian** · 12-06-2011, 04:27 PM

Those both worked - thanks so much, guys!

Velorian

**SeanKosmann** · 12-06-2011, 04:52 PM

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
JC Penny Adam
Best Buy Chris
Best Buy John
Best Buy Arnold
Roy Rogers Walter
Roy Rogers Sarah
Roy Rogers Amy

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

**SeanKosmann** · 12-06-2011, 04:54 PM

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
JC Penny Adam
Best Buy Chris
Best Buy John
Best Buy Arnold
Roy Rogers Walter
Roy Rogers Sarah
Roy Rogers Amy

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

**Haseeb Avarakkan** · 12-06-2011, 05:02 PM

Hello SeanKosmann,

Please read the forum rules, rule # 2 says,

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.

So please start your own thread.