# count unduplicated names in a list

#### cots

##### New Member
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

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
{=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

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.

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
ABCD
1Tom4
2****
3Harry
4Tom
5****
6Harry
7Tom
8****
9Harry
10Fred
11
12
13
Sheet1

Hope this helps,

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

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

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

Fair enough
P

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

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!

Replies
6
Views
84
Replies
9
Views
466
Replies
1
Views
154
Replies
1
Views
199
Replies
6
Views
153

1,203,642
Messages
6,056,511
Members
444,871
Latest member
Vishal Gupta

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back