count unduplicated names in a list

[cots]

is there a formula to count unduplicated names in a list?

for ex.: count a name once even if it is listed twice or more in a list
This message was edited by cots on 2002-08-22 16:57

 

[Mark W.]

{=SUM(1/COUNTIF(A2:A11,A2:A11))}

where column A contains your names.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-08-22 17:02

 

[Aladin Akyurek]

On 2002-08-22 16:56, cots wrote:
is there a formula to count unduplicated names in a list?

for ex.: count a name once even if it is listed twice or more in a list

I guess you didn't do a search on this site...

This array-formula

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

will give you the desired count.

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

If you're willing to install the morefunc add-in, you can just use:

=COUNTDIFF(Range)

which is normally entered.

 

[PaddyD]

Hi - welcome to the board!

Try the following array formula:

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

(enter the formula with control + shift + enter - excel will add curly brackets round it).

Book4
	A	B	C	D
1	Tom	4
2	****
3	Harry
4	Tom
5	****
6	Harry
7	Tom
8	****
9	Harry
10	Fred
11
12
13
Sheet1

Hope this helps,

Paddy

 

[PaddyD]

On 2002-08-22 17:00, Mark W. wrote:
{=SUM(1/COUNTIF(A2:A11,A2:A11))}

where column A contains your names.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-08-22 17:02

Mark,

but by taking the len() check out, you'll end up counting blank cells as well?

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


Paddy

EDIT - what was up with your original post that made you retract it?
This message was edited by PaddyD on 2002-08-22 17:08

 

[Mark W.]

What blanks? There are no blanks in my sample data.

I was calculating the reciprocal for each row and decided to provide 1 array formula instead.
This message was edited by Mark W. on 2002-08-22 17:10

 

[PaddyD]

Fair enough
P

 

[Aladin Akyurek]

On 2002-08-22 17:06, PaddyD wrote:

On 2002-08-22 17:00, Mark W. wrote:
{=SUM(1/COUNTIF(A2:A11,A2:A11))}

where column A contains your names.

Note: This is an array formula which must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
This message was edited by Mark W. on 2002-08-22 17:02

Mark,

but by taking the len() check out, you'll end up counting blank cells as well?

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


Paddy

EDIT - what was up with your original post that made you retract it?
This message was edited by PaddyD on 2002-08-22 17:08

Blanks without the LEN check will cause #DIV/0!

Aladin