subtotal unique values when filtering a list

[shredr]

This is something I would figure is simple in Excel but have yet to figure it out...and I've been trying on and off for a while.

I have a sheet with daily shipments for my company, it's about 10000 lines deep per year.

I applied the data filter to the sheet, and have a couple subtotal functions working, but I cannot figure out how to get a count of unique values when I am filtering.

I've tried different combinations of sum,subtotal and/or countif functions, but I am dead ending with everything.
Looking at various forums I haven't found anything that solves my issue.

I need the unique count of sales orders shown in column A when I apply the filter.
We may ship partials against an order line, so I don't want to keep counting the same line if we ship four times a month against it. (does that make sense?)

Am I making my life too difficult with this?

Thanks for ANY help with this one.
Ted

This is a portion of my sheet.
I threw in some dummy dates to shorten it up and left a couple examples of things I tried at the top of column C

Excel 2010
	A	B	C	D	E	F	G	H
1	0	22	SUM(IF(FREQUENCY(IF(LEN(A6:A10196)>0,MATCH(A6:A10196,A6:A10196,0),""), IF(LEN(A6:A10196)>0,MATCH(A6:A10196,A6:A10196,0),""))>0,1))				count of shipments	22
2	#REF!		SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))				qty parts shipped	4,580
3			SUM(IF(FREQUENCY(A2:A10,A2:A10)>0,1))
4
5	SO #	item	date	quantity	sellum
6	55474-2	002-0602-50	01/02/13	1200	EA
7	55474-5	002-0605-49	01/02/13	150	EA
8	55474-6	002-0616-50	01/02/13	120	EA
9	55474-3	002-0623-50	01/02/13	100	EA
10	55525-2	002-0700-49	01/02/13	10	EA
11	55169-1	002-0782-50	02/02/13	120	EA
12	55170-1	002-0782-9J	02/03/13	450	EA
13	55219-1	002-0785-9J	02/04/13	175	EA
14	55077-1	002-0796-9J	02/05/13	30	EA
15	55172-1	002-0871-9J	02/06/13	30	EA
16	55308-1	002-0955-50	02/07/13	75	EA
17	55305-1	002-1075-9J	02/08/13	600	EA
18	55165-1	002-1076-50	03/02/13	175	EA
19	55220-1	002-1076-9J	03/03/13	450	EA
20	55332-1	002-1078-9J	03/04/13	250	EA
21	55309-1	002-1115-50	03/05/13	100	EA
22	55472-8	002-1293-6B	03/06/13	25	EA
23	55213-1	002-1336-9J	03/07/13	85	EA
24	55474-4	002-1377-50	03/08/13	400	EA
25	55412-3	002-1404-49	03/09/13	15	EA
26	55260-6	002-1459-6B	03/10/13	12	EA
27	55338-4	002-1474-6B	03/11/13	8	EA
test

Cell Formulas
Range	Formula
A1	=SUM(IF(FREQUENCY(A6:A27,A6:A27)>0,1))
A2	=SUBTOTAL(2,IF(FREQUENCY(#REF!,#REF!)>0,1))
H1	=SUBTOTAL(2,D6:D27)
H2	=SUBTOTAL(9,D6:D27)
B1	{=SUM(IF(FREQUENCY(IF(LEN(A6:A27)>0,MATCH(A6:A27,A6:A27,0),""), IF(LEN(A6:A27)>0,MATCH(A6:A27,A6:A27,0),""))>0,1))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A6,ROW(A6:A27)-ROW(A6),,1)),
  IF(A6:A27<>"",MATCH("~"&A6:A27,A6:A27&"",0))),ROW(A6:A27)-ROW(A6)+1),1))

If the relevant entries are not surrounded with special meaning chars like <, the "~"& and &"" bits can be omitted.

 

[shredr]

Aladin:
I would buy you several beers for this if you were here, thank you! Works perfectly!

 

[Aladin Akyurek]

Aladin:
I would buy you several beers for this if you were here, thank you! Works perfectly!

You are welcome. Thanks for the beer.