Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Column
I am trying to get a count of how many items in a spreadsheet I have an
example below. Basically if the number is listed more than once then it is a part to an assembly and it only needs to be counted once so for the below data it would return the number 8 because there are only 8 diffrent numbers. PS-AA1-120 PS-AA1-120 PS-AA1-121 PS-AA1-121 PS-AA1-122 PS-AA1-122 PS-AA1-123 PS-AA1-123 PS-AA2-001 PS-AA2-001 PS-AA2-002 PS-AA2-002 PS-AA2-002 PS-AA2-003 PS-AA2-003 PS-AA2-004 PS-AA2-004 PS-AA2-004 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Column
With your data in Col; try the below formula which will give you the distinct
count =SUMPRODUCT(--(A1:A10<""),1/COUNTIF(A1:A10,A1:A10&"")) If this post helps click Yes --------------- Jacob Skaria "Cul" wrote: I am trying to get a count of how many items in a spreadsheet I have an example below. Basically if the number is listed more than once then it is a part to an assembly and it only needs to be counted once so for the below data it would return the number 8 because there are only 8 diffrent numbers. PS-AA1-120 PS-AA1-120 PS-AA1-121 PS-AA1-121 PS-AA1-122 PS-AA1-122 PS-AA1-123 PS-AA1-123 PS-AA2-001 PS-AA2-001 PS-AA2-002 PS-AA2-002 PS-AA2-002 PS-AA2-003 PS-AA2-003 PS-AA2-004 PS-AA2-004 PS-AA2-004 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Column
Assuming col f. datafilteradvanced filtercopy to sameUNIQUEuse this
formula. =SUBTOTAL(3,F:F)-1 -- Don Guillett Microsoft MVP Excel SalesAid Software "Cul" wrote in message ... I am trying to get a count of how many items in a spreadsheet I have an example below. Basically if the number is listed more than once then it is a part to an assembly and it only needs to be counted once so for the below data it would return the number 8 because there are only 8 diffrent numbers. PS-AA1-120 PS-AA1-120 PS-AA1-121 PS-AA1-121 PS-AA1-122 PS-AA1-122 PS-AA1-123 PS-AA1-123 PS-AA2-001 PS-AA2-001 PS-AA2-002 PS-AA2-002 PS-AA2-002 PS-AA2-003 PS-AA2-003 PS-AA2-004 PS-AA2-004 PS-AA2-004 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting Column
Hi,
Any of the following may work =SUM(1/COUNTIF(A1:A18,A1:A18)) array entered array - press Shift+Ctrl+Enter to enter the formula =SUMPRODUCT(1/COUNTIF(A1:A18,A1:A18)) no array needed. -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Cul" wrote: I am trying to get a count of how many items in a spreadsheet I have an example below. Basically if the number is listed more than once then it is a part to an assembly and it only needs to be counted once so for the below data it would return the number 8 because there are only 8 diffrent numbers. PS-AA1-120 PS-AA1-120 PS-AA1-121 PS-AA1-121 PS-AA1-122 PS-AA1-122 PS-AA1-123 PS-AA1-123 PS-AA2-001 PS-AA2-001 PS-AA2-002 PS-AA2-002 PS-AA2-002 PS-AA2-003 PS-AA2-003 PS-AA2-004 PS-AA2-004 PS-AA2-004 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Counting a Column | Excel Worksheet Functions | |||
Counting items in one column based on criteria in another column | Excel Worksheet Functions | |||
Counting entries in column based on condition in another column | Excel Worksheet Functions | |||
Formula help for counting,with a column of dates and a column of n | Excel Worksheet Functions | |||
Counting names in a column but counting duplicate names once | Excel Discussion (Misc queries) |