| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| For each change in Col. C, Count Unique Numbers in Col. G | Excel Worksheet Functions | |||
| Count unique numbers | Excel Discussion (Misc queries) | |||
| Count unique numbers | Excel Worksheet Functions | |||
| Count Unique Numbers | Excel Worksheet Functions | |||
| summary count of unique numbers | Excel Discussion (Misc queries) | |||
Linear Mode
