Counting the number of unique values in a filtered list

[Bannor]

Hi!

I've been battling with this for a while now, I hope you guys can help!
I'm using Office 2007 Enterprise on Win XP Pro SP3.

I have a sheet of data comprising store names, product quantities, product
prices, etc. The store names are in the first column (text values,
obviously), with product names, prices, quantities, etc in subsequent
columns. There are at present about 50 columns and 2500 rows of data, with no
blank rows. This data is filtered.

What I need to do is to count the number of unique values in a given column
(for example the number of stores that stock a particular product, with the
list having been filtered by that particular product name). I do not need to
know the frequency or the names of the stores - just how many there are in
the filtered list.

The answer given to this particular question
[https://www.microsoft.com/office/co...=en-us-excel&lang=en&cr=US&sloc=en-us&m=1&p=1]
is the closest I have found so far to giving me what I need, but this does
not adjust for when rows are filtered / hidden.

I don't particularly want to use a pivot-table; that would be rather
cumbersome to use easily. I need to use this result in other calculations.
Actually, I'm a bit surprised that a function of this nature has yet to be
built-in to Office 2007.

I hope this doesn't stump you guys like it has me!

 

[OssieMac]

Since you are using a filterd list I assume it is AutoFilter. Is this
correct? Have you tried using SUBTOTAL function. Look it up in Help for all
of the various mathematical calcs it can return and automatically updates
each time the filter is changed.

 

[Gary Keramidas]

have a look at advanced filter, too.

 

[T. Valko]

This will count the unique items in column A of a filtered list.

Assume the full unfiltered range is A2:A20.

Array entered** :

=COUNT(1/FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2,ROW(A2:A20)-ROW(A2),)),MATCH(A2:A20,A2:A20,0)),ROW(A2:A20)-ROW(A2)))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

 

[Bannor]

T.Valko - You're the man!!! Works perfectly! Thankyou very much!

OssieMac & Gary Keramidas - thanks for trying, but I don't believe either
the SUBTOTAL function nor an advanced filter will give me exactly what I was
looking for - I had already looked at both options extensively before my post.

 

[T. Valko]

You're welcome. Thanks for the feedback!