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 |
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 |
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 |
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 |
All times are GMT +1. The time now is 03:05 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com