Home 
Search 
Today's Posts 
#1




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




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




Bruce,
Array enter (enter using CtrlShiftEnter) =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




Perhaps try, arrayentered (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




"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




"Bernie Deitrick" <deitbe @ consumer dot org wrote...
Array enter (enter using CtrlShiftEnter) =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




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




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 CtrlShiftEnter) =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




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, arrayentered (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




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

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
MIN with zero values in the range  Excel Discussion (Misc queries)  
Checking ALL values in a range  Excel Discussion (Misc queries)  
How to move Yaxis values when X range is a to +b  Charts and Charting in Excel  
Counting values within a Date Range  Excel Discussion (Misc queries)  
Can you average data in 1 column based on a range of values in another?  Excel Worksheet Functions 