Brilliant. That seems to work great. I don't understand it, but it works.
What is the "--" for in the syntax?
Maybe you can look at my other question,
"How to CountIf Involving Another Column"?
Thanks a bunch for your time!!
"Max" wrote in message
...
Perhaps try, array-entered (CTRL+SHIFT+ENTER):
=SUM(IF(LEN(rngPlaces)0,1/COUNTIF(rngPlaces,rngPlaces)))-SUM(--(COUNTIF(rng
Places,rngPlaces)=1))
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Bruce Norris" wrote in message
.. .
Could someone help me out on this, please?
I have a single column range as a defined name (rngPlaces).
I want a formula in another cell to count how many values (text) appear
more than once in the range.
So...
- Values that appears only once in the list: Don't count.
- Values that appears more than once in the list: Count the value as 1.
- Blank cells: Don't count.
To further clarify - a value that appears multiple times is only
counted once.
- "Mexico City" appears 50 times: "Mexico City" counted as 1
- "Paris" appears 2 times: "Paris" counted as 1
- "Denver" appears 1 time: "Denver" counted as 0
Any ideas?
Excel 2002.
Thanks.
|