Trying to construct a count count formula

[Guest]

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

 

[T. Valko]

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(Order)-MIN(ROW(Order)+1)))

Biff

 

[T. Valko]

I forgot to add:

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

Biff

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(Order)-MIN(ROW(Order)+1)))

Biff

 

[Guest]

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.

 

[T. Valko]

If different stores have the same order number (if that's possible) that
will fail.

Biff

 

[Guest]

True, but I also tested the other formula on the sample data and it failed on
the last entry.

 

[T. Valko]

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/5083/countuniquesqm2.jpg

Biff

True, but I also tested the other formula on the sample data and it failed
on
the last entry.