Hi Excel People
I have a large list of names in my excel in just one column! I want to count how many these names are different, and I would like to use a formula....any suggestions?
To be clear is basically to count the number of different names in a column,
Thank you so much for your help!
=sum(--(match(diap,diap,)=row(diap))) as CSE
diap's first row is 1
How large is the list?
Try something like this...
=SUMPRODUCT(1/COUNTIF(A1:A100,A1:A100))
Assumes no empty cells within the list.
If the list is 1000's of rows then a better formula can be created.
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
this is not workingcan anyone help me please?
Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.
Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Can you be more specific about what does "not working" mean?Originally Posted by pedro90
Do you get an error? An incorrect result? Something else?
Can you post a SMALL sample file and show us what result you expect?
A SMALL file will have about 20 rows worth of data.
Here you have a template of the excel I would like to work on.... I would like to count how many different countries are in that list...
Hi.
I can't seem to see your attempt at using the solution provided by Tony Valko in this file (?)
Regards
Hi XOR LX,
I tried but unfortunately it gives me an error type !NAME...i guess that his formula would only result with number...not with words...
If your version of Excel is using a language other than English, then you'll need to translate the formula accordingly.
For example, in Portuguese:
=SOMARPRODUTO(1/CONTAR.SE(D5:D36;D5:D36))
(And your end reference should be 36, not 37, otherwise you'll have a circular reference.)
Regards
Hello XOR LX,
I tried this right now and unfortunately did not work as well
Please be more specific than "did not work". What result did you get?
Are you using a Portuguese-language version of Excel or not? If not, which language?
If you are using a Portuguese-language version, please re-upload your workbook with the Portuguese formula I gave you added.
Regards
Yes yes I'm using Portuguese version! Here it is the file ...thanks for the help
I don't understand.
The formula in there is:
=SUMPRODUCT(1/CONTAR.SE(D5:D36,D5:D36))
That's not at all what I gave in post #10.
Regards
Thank you so much for being so pacient with me!
I used this formula that you suggested but maybe I did not save before I show you...
Hmmm.
Did you get a #NAME? error again? Maybe your language version is actually "Brazilian" Portuguese, not "Portuguese" Portuguese?
Perhaps this works for you?
=SOMARPRODUTO(1/CONT.SE(D5:D36;D5:D36))
Regards
It worked
You're welcome!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks