Counting the number of unique values in a filtered list
You're welcome. Thanks for the feedback!
--
Biff
Microsoft Excel MVP
"Bannor" wrote in message
...
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.
--
Philip
"T. Valko" wrote:
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.
--
Biff
Microsoft Excel MVP
|