Counting how many different names in a long list

Q

Quaisne

I have a list of 332 names many of them are duplicated. I am looking for
a formula or function that can count how many unique names there are in
the list.

Can anyone help?
 
D

Dave Peterson

This'll count the number of distinct values in a range:

=SUMPRODUCT((A1:A99<>"")/COUNTIF(A1:A99,A1:A99&""))

(adjust the range to match, but don't use the whole column)
 
Q

Quaisne

That works fine.

When I put it in a cell of its' own it gives the correct answer of 56.
I then wanted to add 2 to it as I know there are 2 lots of duplicate
names that are different people. That worked fine as well with just +2
at the end of the formula.

But when I put it in a concatenate function for some reason it gave the
answer as 58.0000000000001 and it refuses to be formatted, even if I put
it into a seperate hidden cell and format that cell. It appears as 58 on
a cell on its own but as the other value in any text string including
just using &

Still I do not need it in a concatenate function, it just appeared
neater that way. I was just curious though but thanks anyway as that
does solve my problem.
 
D

Dave Peterson

You can use =round() or =text() in your formula that concatenates:

="this is some text: " & text(a1,"#,##0")
or
="this is some text: " & round(a1,0)
 
Q

Quaisne

Unfortunately I still can not get that to work.:confused:

my formula is

=(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&""))+2&" differen
scorers")

How do I incorporate

="this is some text: " & text(a1,"#,##0")
or
="this is some text: " & round(a1,0)

into that to make it work. I either get a value error or the sam
answer. :
 
C

Cutter

In case you're anxious for some help, try this:

=ROUND(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&"")),0)+2&
different scorers
 
Q

Quaisne

Thanks that works fine now.:)

I understood what Dave meant about a1.

What I was doing wrong was putting the ,0 at the very end of th
formula after different scorers.:
 
C

Cutter

Glad you got it working. I know what it's like to ask for help and the
wait for what seems like forever while you're trying hard to ge
something to work
 
Top