| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Trying to construct a count count formula
I have a list of stores with many order numbers, the order numbers are
duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
Try this:
D2 = apost D3 = fone Enter this array formula** in E2 and copy down as needed: =COUNT(1/FREQUENCY(IF(Store=D2,MATCH(Order,Order,0)),ROW(Or der)-MIN(ROW(Order)+1))) Biff "Chris K" wrote in message ... I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
I forgot to add:
** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) Biff "T. Valko" wrote in message ... Try this: D2 = apost D3 = fone Enter this array formula** in E2 and copy down as needed: =COUNT(1/FREQUENCY(IF(Store=D2,MATCH(Order,Order,0)),ROW(Or der)-MIN(ROW(Order)+1))) Biff "Chris K" wrote in message ... I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
Hi Chris,
the following array formula will do what you want: =SUM((IF(A$2:A$14=E2,1/COUNTIF(B$2:B$14,B$2:B$14),""))) Where E2 contains the store name and column A are your stores and column B the invoice numbers. To enter this hold down the Shift and Ctrl keys and press Enter. -- Cheers, Shane Devenshire "Chris K" wrote: I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
If different stores have the same order number (if that's possible) that
will fail. Biff "ShaneDevenshire" wrote in message ... Hi Chris, the following array formula will do what you want: =SUM((IF(A$2:A$14=E2,1/COUNTIF(B$2:B$14,B$2:B$14),""))) Where E2 contains the store name and column A are your stores and column B the invoice numbers. To enter this hold down the Shift and Ctrl keys and press Enter. -- Cheers, Shane Devenshire "Chris K" wrote: I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
True, but I also tested the other formula on the sample data and it failed on
the last entry. -- Thanks, Shane Devenshire "T. Valko" wrote: If different stores have the same order number (if that's possible) that will fail. Biff "ShaneDevenshire" wrote in message ... Hi Chris, the following array formula will do what you want: =SUM((IF(A$2:A$14=E2,1/COUNTIF(B$2:B$14,B$2:B$14),""))) Where E2 contains the store name and column A are your stores and column B the invoice numbers. To enter this hold down the Shift and Ctrl keys and press Enter. -- Cheers, Shane Devenshire "Chris K" wrote: I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Trying to construct a count count formula
The other formula works but will fail if there are empty cells in the order
range. Screencap (includes a SUMPRODUCT version) http://img528.imageshack.us/img528/5...uniquesqm2.jpg Biff "ShaneDevenshire" wrote in message ... True, but I also tested the other formula on the sample data and it failed on the last entry. -- Thanks, Shane Devenshire "T. Valko" wrote: If different stores have the same order number (if that's possible) that will fail. Biff "ShaneDevenshire" wrote in message ... Hi Chris, the following array formula will do what you want: =SUM((IF(A$2:A$14=E2,1/COUNTIF(B$2:B$14,B$2:B$14),""))) Where E2 contains the store name and column A are your stores and column B the invoice numbers. To enter this hold down the Shift and Ctrl keys and press Enter. -- Cheers, Shane Devenshire "Chris K" wrote: I have a list of stores with many order numbers, the order numbers are duplicated. I need to count the unique number of order numbers according to each store without manually removing duplicates etc. within excel 2007. e.g. Store Order Number apost 100124 apost dave101 apost dave101 apost 100024 fone 115 fone 1000214 fone lar1004 fone 1000214 fone 10021 Result apost 3 fone 4 -- Chris K Excel user |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Count Formula - Count Ticks | Excel Discussion (Misc queries) | |||
| Count Employee Work Time - Don't Double-count Overlapping Apts. | Excel Worksheet Functions | |||
| Excel 2000, count, sort a list & count totals? | Excel Worksheet Functions | |||
| Count Intervals of 1 Numeric value in a Row and Return Count down Column | Excel Worksheet Functions | |||
| Count Intervals of Filtered TEXT values in Column and Return Count across a Row | Excel Worksheet Functions | |||
Linear Mode
