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?
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?
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)
Quaisne wrote:
>
> 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: http://www.excelforum.com/member.php...o&userid=28052
> View this thread: http://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 &
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.
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)
Quaisne wrote:
>
> 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.
>
> --
> Quaisne
> ------------------------------------------------------------------------
> Quaisne's Profile: http://www.excelforum.com/member.php...o&userid=28052
> View this thread: http://www.excelforum.com/showthread...hreadid=501357
--
Dave Peterson
Unfortunately I still can not get that to work.
my formula is
=(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&""))+2&" different 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 same answer.
In case you're anxious for some help, try this:
=ROUND(SUMPRODUCT((E2:E336<>"")/COUNTIF(E2:E336,E2:E336&"")),0)+2&" different scorers"
Dave Peterson was suggesting that you put his formula in A1 so wherever A1 appears in his follow-up suggestions you replace it with his initial formula.
Or simply put his original formula in any cell and change the A1 references in his follow-up formulae to the address of the cell you used.
Last edited by Cutter; 01-15-2006 at 04:17 PM.
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.
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks