Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Can anyone tell me if there is a formula that will produce a list of all the
unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Select your range and go to Data | Filter | Advanced Filter | choose Copy to
another location | enter location (say D1) | check Unique Records Only. now in E1, enter the formula =COUNTIF($A$1:$A$1000,D1) and drag it down. hope that helps. "PSmith" wrote in message ... Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Hi,
Select your range of date then data|Filter|Advanced filter select copy to another location and 'Check' Unique value only Enter a location to copy to in the 'Copy to' box and click ok. I copied mine to E1 Put this formula in F1 and drag down to the length of your copied data =COUNTIF($A$1:$A$13,E1) An you should now have the required output. Mike "PSmith" wrote: Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Say your datalist is in A1 to A200.
In B1 enter =A1 In B2 enter this *array* formula: =IF(ISERR(MATCH(0,COUNTIF(B$1:B1,$A$1:$A$200&""),0 )),"",INDEX(IF(ISBLANK( $A$1:$A$200),"",$A$1:$A$200),MATCH(0,COUNTIF(B$1:B 1,$A$1:$A$200&""),0))) -- Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of the regular <Enter, which will *automatically* enclose the formula in curly brackets, which *cannot* be done manually. Also, CSE *must* be used when revising the formula. *After* the CSE entry, copy down until you get errors. This gives you a list of the unique values in Column A. Now, to count each value, simply enter this formula in C1: =COUNTIF(A$1:A$200,B1) And copy down as needed. -- HTH, RD ================================================== === Please keep all correspondence within the Group, so all may benefit! ================================================== === "PSmith" wrote in message ... Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Thanks for your responses Gaurav and Mike H. However, when I follow your
instructions, nothing is actually copied into the cell I specify (it remains blank). Any idea on what I might be doing wrong? Thanks again. "PSmith" wrote: Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Thanks to you both, I got it to work but have just one remaining question: If
the range of data is very large (e.g. 10,000), then it is not simple because the formula has to be copied (or dragged) down to the newcorresponding range (of equal length). Any way of avoiding this? Thanks "Mike H" wrote: Hi, Select your range of date then data|Filter|Advanced filter select copy to another location and 'Check' Unique value only Enter a location to copy to in the 'Copy to' box and click ok. I copied mine to E1 Put this formula in F1 and drag down to the length of your copied data =COUNTIF($A$1:$A$13,E1) An you should now have the required output. Mike "PSmith" wrote: Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Thanks, after several repeated attempts I finally got it to work - although I
did not alter what I was doing each time. Why it wouldn't work initially, who knows. But thanks, your help is much appreciated. "PSmith" wrote: Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Frequency of text values
Just double-click on the fill handle.
-- David Biddulph "PSmith" wrote in message ... Thanks to you both, I got it to work but have just one remaining question: If the range of data is very large (e.g. 10,000), then it is not simple because the formula has to be copied (or dragged) down to the newcorresponding range (of equal length). Any way of avoiding this? Thanks "Mike H" wrote: Hi, Select your range of date then data|Filter|Advanced filter select copy to another location and 'Check' Unique value only Enter a location to copy to in the 'Copy to' box and click ok. I copied mine to E1 Put this formula in F1 and drag down to the length of your copied data =COUNTIF($A$1:$A$13,E1) An you should now have the required output. Mike "PSmith" wrote: Can anyone tell me if there is a formula that will produce a list of all the unique text values in a range, and what their corresponding counts are? I am already familiar with the COUNTIF formula, but it can only be used if one already knows what all of the possible values are in the data range. If the list of possible values is long, it would take too many individual formulas to do them one at a time. So, if my data range is something like: Col A. blue red green yellow red black yellow blue red brown brown orange I am hoping I can use one formula that will produce a result that will look like blue 2 green 1 yellow 2 red 3 black 1 brown 2 orange 1 Any suggestions would be greatly appreciated! Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
count frequency of two values in same row | Excel Worksheet Functions | |||
Frequency of values with Criteria | Excel Worksheet Functions | |||
Chart to report frequency of a value as a percentage of values | Charts and Charting in Excel | |||
Combine FREQUENCY and SUM of Associated Values | Excel Worksheet Functions | |||
Counting unique values + frequency | Excel Worksheet Functions |