Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Bruce Norris
 
Posts: n/a
Default 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   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.




  #3   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

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   Report Post  
Max
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"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   Report Post  
Harlan Grove
 
Posts: n/a
Default

"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   Report Post  
Harlan Grove
 
Posts: n/a
Default

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






  #8   Report Post  
Bruce Norris
 
Posts: n/a
Default

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   Report Post  
Bruce Norris
 
Posts: n/a
Default

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 <atyahoo<dotcom
----
"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   Report Post  
Bruce Norris
 
Posts: n/a
Default

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




  #11   Report Post  
Harlan Grove
 
Posts: n/a
Default

"Bruce Norris" wrote...
"Harlan Grove" wrote in message

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

....
. . . Can you see any advantage to yours over the one Max offered?


Which is (using same range name)

=SUM(IF(LEN(Rng)0,1/COUNTIF(Rng,Rng)))-SUM(--(COUNTIF(Rng,Rng)=1))

Max's might be more recalc efficient, but array formulas can be perverse at
times. Max's also is shorter, which is usually better. However, I didn't
need one of my numerator tests since blank cells in Rng have count 0, so
make that

=SUMPRODUCT((COUNTIF(Rng,Rng)1)/(COUNTIF(Rng,Rng)+(Rng="")))

which is now shortest.


  #12   Report Post  
Max
 
Posts: n/a
Default

"Bruce Norris" wrote
Brilliant. That seems to work great.
.. I don't understand it, but it works.


Some clarifications ..

SUM(IF(LEN(rngPlaces)0,1/COUNTIF(rngPlaces,rngPlaces)))

The 1st part of the formula (above) returns the
count of all unique items in the range

SUM(--(COUNTIF(rngPlaces,rngPlaces)=1))

The 2nd part of the formula (above) returns the
count of unique items which appear only *once* in the range
(This count has to be subtracted from the 1st part of the formula
according to your specs)

So the net return (1st - 2nd) gives the desired result

What is the "--" for in the syntax?


In ... SUM(--(COUNTIF(rngPlaces,rngPlaces)=1))

The "--" is to coerce the TRUE / FALSE returns by the COUNTIF(...)
into 1's and 0's for SUM(...) to return the total

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----


  #13   Report Post  
Bernie Deitrick
 
Posts: n/a
Default

Bruce,

Just for completeness - the correct form of my formula to ignore blanks is
to array enter

=SUM(IF(COUNTIF(rngplaces,rngplaces)1,1/COUNTIF(rngplaces,rngplaces),0))

Sorry that I missed that requirement.

HTH,
Bernie
MS Excel MVP


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








Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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 10:44 PM
Checking ALL values in a range nospaminlich Excel Discussion (Misc queries) 13 February 10th 05 10:29 AM
How to move Y-axis values when X range is -a to +b [email protected] Charts and Charting in Excel 4 February 1st 05 12:54 AM
Counting values within a Date Range Jana Excel Discussion (Misc queries) 7 December 9th 04 11: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 03:09 PM


All times are GMT +1. The time now is 01:47 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"