Count Unique Entries

[iszlq]

I'm sure this has been answered before - is there a simpl formula that will allow me to count only unique cells that match criteria? For example - list includes multiple entries for the same person - want to count the person only once.

 

[Megan]

Try looking at this website, it should help you.

http://www.cpearson.com/excel/duplicat.htm

 

[Aladin Akyurek]

On 2002-08-01 10:28, iszlq wrote:
I'm sure this has been answered before - is there a simpl formula that will allow me to count only unique cells that match criteria? For example - list includes multiple entries for the same person - want to count the person only once.

Be specific: What range does the data occupy and what is the criteria?

 

[iszlq]

Specifically, I am trying to count instances. For example, I have a list of names that includes the same name more than once -

Jane Doe
Joe James
Jane Doe
Tom SoAndSo

I want to count how many people are in this list - but I don't want to count Jane Doe twice.

Do I need to use an array function to do this or is there a simple formula?

 

[Aladin Akyurek]

On 2002-08-01 13:09, iszlq wrote:
Specifically, I am trying to count instances. For example, I have a list of names that includes the same name more than once -

Jane Doe
Joe James
Jane Doe
Tom SoAndSo

I want to count how many people are in this list - but I don't want to count Jane Doe twice.

Do I need to use an array function to do this or is there a simple formula?

Three Options:

=COUNTDIFF(A1:A100)

which requires that you add the morefunc add-in, downloadable from:

http://longre.free.fr/english/index.html

or, array-enter:

=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

To array-enter a formula, hit control+shift+enter at the same time, not just enter.

Use Advanced Filter with the filter in place and unique records only options then apply:

=SUBTOTAL(3,A1:A10)

Aladin

 

[iszlq]

or, array-enter:
=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

That worked beautifully, thank you.

What is this formula actually doing though? When I break it down, it makes no sense to me. Len(range) is the test? How does that work? Also, what is countif doing with the range and criteria the same?

How does one ever come upon a formula like this??

 

[Aladin Akyurek]

On 2002-08-02 06:17, iszlq wrote:

or, array-enter:
=SUM(IF(LEN(A1:A10),1/COUNTIF(A1:A10,A1:A10)))

That worked beautifully, thank you.

What is this formula actually doing though? When I break it down, it makes no sense to me. Len(range) is the test? How does that work? Also, what is countif doing with the range and criteria the same?

How does one ever come upon a formula like this??

How does one ever come upon a formula like this??

By une crise d'intelligence maybe. I don't think David Hager is going to like this. On a serious note, I didn't see any account by Hager how he achieved this.

The formula

=SUM(1/COUNTIF(Range,Range))

is attributed to Hager; The IF(LEN(Range) test, which has been tossed in at the old board (I consider myself in part the source for that & Juan), is better than an IF(ISBLANK(Range) test: The idea is NOT to feed any cell which is empty or houses a formula generated "" -- otherwise the 1/COUNTIF(Range,Range) will result in #DIV/0!.
LEN will catch "", while ISBLANK, as one should expect, will not.

What is this formula actually doing though?

Let A1:A3 house {"a";"a";"b"}.

COUNTIF(A1:A3) ==> {2;2;1}

1/{2;2;1} ==> {.5;.5;1}

SUM({.5;.5;1}) ==> 2, the desired count.

You can use the F9 technique in order to see to what a given part of a formula evaluates. For a description of this technique, see:

http://www.mrexcel.com/wwwboard/messages/8961.html

Aladin