software

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?--
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
View this thread: www.excelforum.com/showthread...hreadid=501357This'll count the number of distinct values in a range:

=SUMPRODUCT((A1:A99lt;gt;quot;quot;)/COUNTIF(A1:A99,A1:A99amp;quot;quot;))

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

Quaisne wrote:
gt;
gt; I have a list of 332 names many of them are duplicated. I am looking for
gt; a formula or function that can count how many unique names there are in
gt; the list.
gt;
gt; Can anyone help?
gt;
gt; --
gt; Quaisne
gt; ------------------------------------------------------------------------
gt; Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
gt; View this thread: www.excelforum.com/showthread...hreadid=501357

--

Dave Peterson


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 amp;

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.--
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
View this thread: www.excelforum.com/showthread...hreadid=501357You can use =round() or =text() in your formula that concatenates:

=quot;this is some text: quot; amp; text(a1,quot;#,##0quot;)
or
=quot;this is some text: quot; amp; round(a1,0)

Quaisne wrote:
gt;
gt; That works fine.
gt;
gt; When I put it in a cell of its' own it gives the correct answer of 56.
gt; I then wanted to add 2 to it as I know there are 2 lots of duplicate
gt; names that are different people. That worked fine as well with just 2
gt; at the end of the formula.
gt;
gt; But when I put it in a concatenate function for some reason it gave the
gt; answer as 58.0000000000001 and it refuses to be formatted, even if I put
gt; it into a seperate hidden cell and format that cell. It appears as 58 on
gt; a cell on its own but as the other value in any text string including
gt; just using amp;
gt;
gt; Still I do not need it in a concatenate function, it just appeared
gt; neater that way. I was just curious though but thanks anyway as that
gt; does solve my problem.
gt;
gt; --
gt; Quaisne
gt; ------------------------------------------------------------------------
gt; Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
gt; View this thread: www.excelforum.com/showthread...hreadid=501357

--

Dave Peterson


Unfortunately I still can not get that to work.

my formula is

=(SUMPRODUCT((E2:E336lt;gt;quot;quot;)/COUNTIF(E2:E336,E2:E336amp;quot;quot;)) 2amp;quot; different
scorersquot;)

How do I incorporate

=quot;this is some text: quot; amp; text(a1,quot;#,##0quot;)
or
=quot;this is some text: quot; amp; round(a1,0)

into that to make it work. I either get a value error or the same
answer. --
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
View this thread: www.excelforum.com/showthread...hreadid=501357
In case you're anxious for some help, try this:

=ROUND(SUMPRODUCT((E2:E336lt;gt;quot;quot;)/COUNTIF(E2:E336,E2:E336amp;quot;quot;)),0) 2amp;quot;
different scorersquot;--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=501357
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 the
formula after different scorers.--
Quaisne
------------------------------------------------------------------------
Quaisne's Profile: www.excelforum.com/member.php...oamp;userid=28052
View this thread: www.excelforum.com/showthread...hreadid=501357
Glad you got it working. I know what it's like to ask for help and then
wait for what seems like forever while you're trying hard to get
something to work.--
Cutter
------------------------------------------------------------------------
Cutter's Profile: www.excelforum.com/member.php...foamp;userid=9848
View this thread: www.excelforum.com/showthread...hreadid=501357