View Single Post
  #2   Report Post  
David McRitchie
 
Posts: n/a
Default

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.