Count equal numbers as unique numbers
 

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

Count equal numbers as unique numbers
 

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.

--
R. Khoshravan
Please click "Yes" if it is helpful.


"auyantepui" wrote:

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

Count equal numbers as unique numbers
 

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
---
"auyantepui" wrote:
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

Count equal numbers as unique numbers
 

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


"auyantepui" wrote:

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

Count equal numbers as unique numbers
 

Based on your posted sample data being numeric:

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


--
Biff
Microsoft Excel MVP


"auyantepui" wrote:

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

Count equal numbers as unique numbers
 

Biff, very smart, thx.

"T. Valko" wrote:

Based on your posted sample data being numeric:

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


--
Biff
Microsoft Excel MVP


"auyantepui" wrote:

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

Count equal numbers as unique numbers
 

Thanks!

--
Biff
Microsoft Excel MVP


"Alojz" wrote:

Biff, very smart, thx.

"T. Valko" wrote:

Based on your posted sample data being numeric:

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


--
Biff
Microsoft Excel MVP


"auyantepui" wrote:

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

Count equal numbers as unique numbers
 

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.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"auyantepui" wrote:

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