Counting unique cells (with text) in a filtered list

[Manosh]

Hi
Is there a simple way to count unique text values in the 'header" of a
column where the adjoining column has had the filter switched on?

Example
A Home
B Car
B Home
C Home
C Home
D Car

If filtered on Home in the second column, should show 3 (ie A,B and
C).

Thanks in advance

 

[T. Valko]

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1)),MATCH(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0,1))

** 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.

 

[Manosh]

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1)),MATCH­(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0,1))

** 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









- Show quoted text -

Thanks Biff
I am not getting this to work when i copy paste it into my location
and adjust the ColRow reference and press control shift enter. It
gives me a Name error.
Where should this be - ie preceding the information of the 1st or 2nd
column? Also my actual data starts in line 5 with the filter in 4
whilst these subtotals are in line 1.

 

[T. Valko]

my actual data starts in line 5 with the filter in

4 whilst these subtotals are in line 1

Here's a small sample file that demonstrates this.

Count uniques filter.xls 14kb

http://cjoint.com/?dAdAmy3zD8

--
Biff
Microsoft Excel MVP

Try this array formula** :

=SUM(IF(FREQUENCY(IF(SUBTOTAL(3,OFFSET(A2:A7,ROW(A2:A7)-ROW(A2),0,1)),MATCH­(A2:A7,A2:A7,0)),ROW(A2:A7)-ROW(A2)+1)>0,1))

** 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









- Show quoted text -

Thanks Biff
I am not getting this to work when i copy paste it into my location
and adjust the ColRow reference and press control shift enter. It
gives me a Name error.
Where should this be - ie preceding the information of the 1st or 2nd
column? Also my actual data starts in line 5 with the filter in 4
whilst these subtotals are in line 1.

 

[Manosh]

Here's a small sample file that demonstrates this.

Count uniques filter.xls  14kb

http://cjoint.com/?dAdAmy3zD8

--
Biff
Microsoft Excel MVP





Thanks Biff
I am not getting this to work when i copy paste it into my location
and adjust the ColRow reference and press control shift enter. It
gives me a Name error.
Where should this be - ie preceding the information of the 1st or 2nd
column? Also my actual data starts in line 5 with the filter in 4
whilst these subtotals are in line 1.- Hide quoted text -

- Show quoted text -

Great - it works! thanks for the xls

 

[T. Valko]

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP

Here's a small sample file that demonstrates this.

Count uniques filter.xls 14kb

http://cjoint.com/?dAdAmy3zD8

--
Biff
Microsoft Excel MVP





Thanks Biff
I am not getting this to work when i copy paste it into my location
and adjust the ColRow reference and press control shift enter. It
gives me a Name error.
Where should this be - ie preceding the information of the 1st or 2nd
column? Also my actual data starts in line 5 with the filter in 4
whilst these subtotals are in line 1.- Hide quoted text -

- Show quoted text -

Great - it works! thanks for the xls