If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 How many values appear more than once in a range?
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## How many values appear more than once in a range?

#1
April 5th 05, 12:24 AM
 Bruce Norris external usenet poster Posts: n/a
How many values appear more than once in a range?

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.

#2
April 5th 05, 01:14 AM
 David McRitchie external usenet poster Posts: n/a

Hi Bruce,

=COUNTIF(A\$2:A9,A9)
=COUNTIF(rngPlaces,"Mexico City")

=(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.
>
>

#3
April 5th 05, 02:26 AM
 Bernie Deitrick external usenet poster Posts: n/a

Bruce,

Array enter (enter using Ctrl-Shift-Enter)

=SUM(IF(1/COUNTIF(rngPlaces,rngPlaces)<1,1/COUNTIF(rngPlaces,rngPlaces),0))

will work as long as rngPlaces isn't a complete column (must be less than
65536 cells long)

HTH,
Bernie
MS Excel MVP

"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.
>
>

#4
April 5th 05, 02:47 AM
 Max external usenet poster Posts: n/a

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 <at>yahoo<dot>com
----
"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.
>
>

#5
April 5th 05, 04:01 AM
 Harlan Grove external usenet poster Posts: n/a

"Bruce Norris" > wrote...
....
>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.

....

Another alternative,

=SUMPRODUCT((Rng<>"")*(COUNTIF(Rng,Rng)>1)/(COUNTIF(Rng,Rng)+(Rng="")))

#6
April 5th 05, 04:04 AM
 Harlan Grove external usenet poster Posts: n/a

"Bernie Deitrick" <deitbe @ consumer dot org> wrote...
>Array enter (enter using Ctrl-Shift-Enter)
>
>=SUM(IF(1/COUNTIF(rngPlaces,rngPlaces)<1,1/COUNTIF(rngPlaces,rngPlaces),0))

....

You didn't test this with the OP's specs in mind. If the OP's range contains
blanks (and since the OP explicitly mentions this case, whatcha suppose the
odds are that there will be some blank cells?), your formula returns #DIV/0!

#7
April 5th 05, 05:40 AM
 Bruce Norris external usenet poster Posts: n/a

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")
>
> =(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.
>>
>>

>
>

#8
April 5th 05, 05:46 AM
 Bruce Norris external usenet poster Posts: n/a

Oh, that was soooo close, but like Harlan points out, I do have some blanks
involved. Bummer.

Maybe there's no way to do this with just a formula?

"Bernie Deitrick" <deitbe @ consumer dot org> wrote in message
...
> Bruce,
>
> Array enter (enter using Ctrl-Shift-Enter)
>
> =SUM(IF(1/COUNTIF(rngPlaces,rngPlaces)<1,1/COUNTIF(rngPlaces,rngPlaces),0))
>
> will work as long as rngPlaces isn't a complete column (must be less than
> 65536 cells long)
>
> HTH,
> Bernie
> MS Excel MVP
>
>
>
>
>
> "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.
>>
>>

>
>

#9
April 5th 05, 05:51 AM
 Bruce Norris external usenet poster Posts: n/a

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 <at>yahoo<dot>com
> ----
> "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.
>>
>>

>
>

#10
April 5th 05, 05:55 AM
 Bruce Norris external usenet poster Posts: n/a

"Harlan Grove" > wrote in message
...
> "Bruce Norris" > wrote...
> ...
>>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.

> ...
>
> Another alternative,
>
> =SUMPRODUCT((Rng<>"")*(COUNTIF(Rng,Rng)>1)/(COUNTIF(Rng,Rng)+(Rng="")))

That works too! Thanks, Harlan. I have to admit, I'm more used to Sumproduct
functions. Can you see any advantage to yours over the one Max offered?

Would you mind looking at my other question,
"How to countif involving another column"?

Thanks for all your time, Harlan.

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post MIN with zero values in the range Brenda Rueter Excel Discussion (Misc queries) 5 March 15th 05 09:44 PM Checking ALL values in a range nospaminlich Excel Discussion (Misc queries) 13 February 10th 05 09:29 AM How to move Y-axis values when X range is -a to +b [email protected] Charts and Charting in Excel 4 January 31st 05 11:54 PM Counting values within a Date Range Jana Excel Discussion (Misc queries) 7 December 9th 04 10:18 PM Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 02:09 PM

All times are GMT +1. The time now is 02:25 AM.