Count unique/distinct items in filtered column

[dragonmouse]

I'm going to do my best to explain:

I have several colums with various pieces of equipment, serials and years. I may filter on one column, then I want to count the number of unique pieces of equipment left...Ultimately I may filter differently.

Example:
A B C
YEAR SERIAL Equipment
2010 123 Toaster
2011 256 Refridgerator
2010 175 Oven
2009 123 Toaster
2012 123 Refridgerator
2010 123 Toaster
2012 116 Toaster


For this example I will filter everything in "2010" and it will return: I want to see this

A B C
YEAR SERIAL Equipment
2010 123 Toaster
2010 175 Oven
2010 123 Toaster

When I run a "COUNT" of unique serials I want it to return a total of:

A B C
YEAR SERIAL Equipment
2010 123 Toaster
2010 175 Oven
2010 123 Toaster
Total 2

I don't want it to count any serials that have been "filtered out" and I don't want it to count "duplicate" serial numbers in the filtered return.

Any ideas? I hope I explained it well enough

 

[smkyle]

are you willing to use a pivot table?

 

[Robert Mika]

I'm going to do my best to explain:

I have several colums with various pieces of equipment, serials and years. I may filter on one column, then I want to count the number of unique pieces of equipment left...Ultimately I may filter differently.

Example:
A B C
YEAR SERIAL Equipment
2010 123 Toaster
2011 256 Refridgerator
2010 175 Oven
2009 123 Toaster
2012 123 Refridgerator
2010 123 Toaster
2012 116 Toaster


For this example I will filter everything in "2010" and it will return: I want to see this

A B C
YEAR SERIAL Equipment
2010 123 Toaster
2010 175 Oven
2010 123 Toaster

When I run a "COUNT" of unique serials I want it to return a total of:

A B C
YEAR SERIAL Equipment
2010 123 Toaster
2010 175 Oven
2010 123 Toaster
Total 2

I don't want it to count any serials that have been "filtered out" and I don't want it to count "duplicate" serial numbers in the filtered return.

Any ideas? I hope I explained it well enough

Excel 2010
	A	B	C	D	E	F
1	YEAR	SERIAL	Equipment			2
2	2010	123	Toaster
4	2010	175	Oven
7	2010	123	Toaster
Sheet4

Cell Formulas
Range	Formula
F1	{=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(B2:B20)-ROW(A2),)),MATCH(B2:B20,B2:B20,0)),ROW(B2:B20)-ROW(A2)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[dragonmouse]

No...not a PIVOT table. There's a lot more data. I just want to total it at the bottom. This did not work unfortunately.

 

[Robert Mika]

No...not a PIVOT table. There's a lot more data. I just want to total it at the bottom. This did not work unfortunately.

Did you try the formula?Why it did not work?

 

[dragonmouse]

I'm not sure I understand the "A2" in the (A2,ROW(B2:B20) I might be doing that part wrong. It came out with a total of ZERO.

It seems like I used somethign to the effect of =COUNT(Sumproduct(B2:b5, b2:b5) But for the life of me I can't remember the exact forumla.

 

[dragonmouse]

I dug this up...and it works for "FILTERED" items returning a count...but it doesn't eliminate the DUPLICATES. (if I have two items with the same serial number in the "filtered year" 2010 I only want to count one)

"=SUBTOTAL(3,B6:B13547)"

I've been trying the formula mentioned in an earlier post but I'm doing something horribly wrong.