Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting groups of exact numbers in a huge list (column)
This formula has been a great help to me. Thanks for the post; however what
if the items have an alpha-numeric coding (like automobile license plates)? Is there an easy way to count unique labels in a list when the data in in a text format? Thanks "tjtjjtjt" wrote: If they are all numbers: =SUM(IF(FREQUENCY(A1:A11,A1:A11)0,1)) You can see this page for variations and details: http://support.microsoft.com/kb/q268001/ The formula is an Array, so you should press Ctrl+Shift+Enter to finish it. tj "*Jarom*" wrote: I need to know how I can write a formula to count a very big list of numbers and return the total number of numbers that are different in the list. For example: 8800719 8800718 8800718 8800717 8800719 8800715 8800719 8800715 As you can see there are 8 numbers in this list but of those 8 there are only 4 different case numbers. So if I had a list of several hundred or thousand case numbers like the ones above, and many of them had duplicates somewhere in the list, how can I get excel to give me a total number of all the similar case numbers? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Counting groups of exact numbers in a huge list (column)
=SUMPRODUCT((A1:A11<""(/COUNTIF(A1:A11,A1:A11&""))
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "pgiessler" wrote in message ... This formula has been a great help to me. Thanks for the post; however what if the items have an alpha-numeric coding (like automobile license plates)? Is there an easy way to count unique labels in a list when the data in in a text format? Thanks "tjtjjtjt" wrote: If they are all numbers: =SUM(IF(FREQUENCY(A1:A11,A1:A11)0,1)) You can see this page for variations and details: http://support.microsoft.com/kb/q268001/ The formula is an Array, so you should press Ctrl+Shift+Enter to finish it. tj "*Jarom*" wrote: I need to know how I can write a formula to count a very big list of numbers and return the total number of numbers that are different in the list. For example: 8800719 8800718 8800718 8800717 8800719 8800715 8800719 8800715 As you can see there are 8 numbers in this list but of those 8 there are only 4 different case numbers. So if I had a list of several hundred or thousand case numbers like the ones above, and many of them had duplicates somewhere in the list, how can I get excel to give me a total number of all the similar case numbers? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop down list shrinks as you go down column | Excel Discussion (Misc queries) | |||
Match Column B numbers to Column A numbers | Excel Worksheet Functions | |||
Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
Return Count for LAST NonBlank Cell in each Row | Excel Worksheet Functions | |||
Counting numbers in a column without including others | Excel Worksheet Functions |