Count equal numbers as unique numbers

[auyantepui]

how can I count equal numbers in colum A as unique ones? for example:

Regardless there are 3 (6452301) i need to count them as one Sales Order and
not as three, as so on:

6452302
6452301
6452301
6452301
6452303
6452303
6452303

 

[Khoshravan]

One way is to get the list of unique entries.
Go to Data| Filter| Advanced filter.
Select: Copy to another location
Select the range you want to get its unique entries
Tick the Unique records only box in the bottom.
This will give you the unique entries.

 

[Max]

One way
In say, B1:
=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))
High-five? Click YES button below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,500 Files:370 Subscribers:66
xdemechanik

 

[Alojz]

If ur area does not contain blank cells, the following should work:
=SUM(1/COUNTIF(A1:A10,A1:A10)) press ctrl+shift+enter as this is array formula

HTH

 

[T. Valko]

Based on your posted sample data being numeric:

=SUM(--(FREQUENCY(A1:A20,A1:A20)>0))

 

[Alojz]

Biff, very smart, thx.

Based on your posted sample data being numeric:

=SUM(--(FREQUENCY(A1:A20,A1:A20)>0))

 

[T. Valko]

Thanks!

--
Biff
Microsoft Excel MVP

Biff, very smart, thx.

 

[Shane Devenshire]

Hi,

You can also avoid the array by using

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

We can't tell if your sales order numbers are text or numbers, if they are
text the FREQUENCY function needs to be modified:

=SUM(--(FREQUENCY(--B1:B7,--B1:B20)>0))

This will return an incorrect result if any cells are blank so you would
need to modify it to

=SUM(--(FREQUENCY(--B1:B20,--B1:B20)>0),-1)

But now this would return an incorrect result if no cells were blank, so

=SUM(--(FREQUENCY(--B1:B20,--B1:B20)>0),-(COUNTBLANK(B1:B20)>0))

works in both cases. The key message here is that FREQUENCY works only with
numerical data while COUNTIF works with any kind of data.