Count unique values

[fsfiligoi]

I need some help from the experts! I'm trying to count how many documents
each employee created using this info:
Name Doc #
Joe 20
Joe 21
Joe 21
Joe 21
Paul 34
Paul 34
Paul 35
Paul 35
Paul 36
The result here would be: Joe created 2 documents and Paul created 3
documents.
How could I do it? I can't use filter because I have a lot of data.

 

[T. Valko]

Assuming there are no empty cells in the document range.

Try this array formula** :

=COUNT(1/FREQUENCY(IF(A2:A10="Joe",B2:B10),B2:B10))

Or, use cells to hold the name:

D2 = Joe
D3 = Paul

=COUNT(1/FREQUENCY(IF(A$2:A$10=D2,B$2:B$10),B$2:B$10))

Then copy down as needed.


** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[Ron Coderre]

With your posted data in A1:B10

Try this:
D2: Joe

This ARRAY FORMULA, committed with CTRL+SHIFT+ENTER,
instead of just ENTER, counts the unique doc numbers for Joe:
E2:
=COUNT(1/FREQUENCY(IF((A$2:A$20=D2)*ISNUMBER($B$2:$B$20),B$2:B$20),B$2:B$20))

Is that something you can work with?
Post back if you have more questions.

Regards,

Ron
Microsoft MVP - Excel

 

[fsfiligoi]

Thank you so much guys!! I feel embarrased that it took me the whole morning
to do it manually and 1 minute to do it using the formula! I'll be using
this formula quite a bit! I tried both but for some reason only T. Valko's
formula worked.
Thanks again!
Fatima

 

[fsfiligoi]

It looks like this formula works only with numbers. If I have duplicate text
or a dates instead of document numbers, do you know what I should to change
to make it work?
Am I asking too much now? : )

 

[T. Valko]

Am I asking too much now? : )

It depends on who you ask! <g>

Assuming there are no empty cells in the document range.

Array entered** :

=COUNT(1/FREQUENCY(IF(A2:A10=D2,MATCH(B2:B10,B2:B10,0)),ROW(B2:B10)-MIN(ROW(B2:B10))+1))

Where D2 = name

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)