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

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

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

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

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

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

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