Hi Bruce,
=COUNTIF(A$2:A9,A9)
=COUNTIF(rngPlaces,"Mexico City")
with your restriction
=(COUNTIF(rngPlaces,"Mexico City")1)+0
Summarizing Data Examples (an Overview)
http://www.mvps.org/dmcritchie/excel/sumdata.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages:
http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page:
http://www.mvps.org/dmcritchie/excel/search.htm
"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.