Counting unique cells (with text) in a filtered list

  • Thread starter Thread starter Manosh
  • Start date Start date
M

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
 
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.
 
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 -
Click to expand...

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.
 
my actual data starts in line 5 with the filter in
4 whilst these subtotals are in line 1
Click to expand...

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 -
Click to expand...

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.
 
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 -
Click to expand...

Great - it works! thanks for the xls
 
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 -
Click to expand...

Great - it works! thanks for the xls
 
Back
Top