Counting unique shipments

[karibuckeye]

I need a way to count the unique numbers in this column in a pivot table. Ideally I do not want sort the data in the column either. Is this possible? So I want to see in a pivot table the count of unique shipments is 5 (12,25,26,27,28).

[TABLE="width: 500"]
<tbody>[TR]
[TD]Number[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
[TR]
[TD]25[/TD]
[/TR]
[TR]
[TD]26[/TD]
[/TR]
[TR]
[TD]27[/TD]
[/TR]
[TR]
[TD]28[/TD]
[/TR]
[TR]
[TD]27[/TD]
[/TR]
[TR]
[TD]12[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jon von der Heyden]

What version of Excel are you using? If 2013/16 then pivot table Distinct Count summary function may be what you are after.

See here: http://blog.contextures.com/archives/2016/02/25/count-unique-in-excel-pivot-table/

 

[karibuckeye]

Yea i'm using 2010 :/

Is there a formula I can put in my dataset so that my data looks this:


[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Number Unique Count[/TD]
[/TR]
[TR]
[TD]12 1[/TD]
[/TR]
[TR]
[TD]12 0[/TD]
[/TR]
[TR]
[TD]25 1[/TD]
[/TR]
[TR]
[TD]26 1[/TD]
[/TR]
[TR]
[TD]27 1[/TD]
[/TR]
[TR]
[TD]28 1[/TD]
[/TR]
[TR]
[TD]27 0[/TD]
[/TR]
[TR]
[TD]12 0[/TD]
[/TR]
</tbody>[/TABLE]

 

[S.H.A.D.O.]

You could give this a try, change the range to suit.

=SUMPRODUCT(1/COUNTIF(A1:A10,A1:A10))

I hope this helps!

 

[karibuckeye]

That didn't work That formula should work if I use a range right? So I'm typing in =SUMPRODUCT(1/COUNTIF(A:A,A:A)); i'm getting an error? Does column A need to be formatted as a value or text?

 

[S.H.A.D.O.]

That didn't work That formula should work if I use a range right? So I'm typing in =SUMPRODUCT(1/COUNTIF(A:A,A:A)); i'm getting an error? Does column A need to be formatted as a value or text?

That formula will only work on a specified range that doesn't include any blank or empty cells.

 

[karibuckeye]

Oh ok, well because month over month the range will change I'm not sure this is the best formula for me then?

Any other ideas for me to try?

I really appreciate the help!