Formula - How many values appear more than once in a range?

[Gregg]

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.

- Value appears once in the list: Don't count.
- Value appears more than once in the list: Count.
- Value is blank: Don't count.

Any ideas?

Excel 2002.

Thanks.

 

[Gregg]

I wanted to clarify that 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

Thanks.

 

[excelmunkey]

Hi Gregg,

Say your range is in column A and your place to be counted is in column
B and the count is in C, then use the formula
=IF(COUNTIF(PLACES,B1)>1,1,"") to give:

Mexico City 1
Paris 1
Denver

The "" at the end of the formula means that if the count of place in
the range is 1 or less then a blank will be returned, so this will deal
with any place names not present in the range.

Cheers,
JF.