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

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

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

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