View Single Post
  #7   Report Post  
Bruce Norris
 
Posts: n/a
Default

Hmmm. I see what your formulas do, but was looking more to answer this
question...
"How many cities in the list appear more than once?"

So, I don't want to only count "Mexico City", but any and all cities that
appear more than once. Again, I'm looking for a formula in one cell to do
this. I know I can put a special countif in an additional column within each
record then sum that up or whatever. But I'm hoping for one formula to do
it.

Any thoughts?


"David McRitchie" wrote in message
...
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.