Count Filter

[Mandeep Dhami]

Dear All,

Is there any formula by which we can count number of different data entered
in a column after filtiring the column.

I mean to say......if in column A......we enter names of various cities
around the world. Now when we filter by say New York we get column A with New
York......now I want to know how many different cities are entered in column
A.
For example there may be only 15 cities but they me be repeated 100 times in
column A.

Cheers,
Mandeep Dhami

 

[David McRitchie]

You could filter using unique records see
http://www.contextures.com/tiptech.html
advanced filter, unique records

 

[Bob Phillips]

=SUMPRODUCT((A2:A200<>"")/COUNTIF(A2:A200,A2:A200&""))

will give the number regardless of filtered or not.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Mandeep Dhami]

Sorry Bob not able to understand your formula. If you can explain a bit.

My requirement is as follows:
Column A
Name of City
New York
London
New York
New York
London

In this example New York is repeated 3 times and London is repeated 2 times.
I want a seperate list showing various cities in column A as shown in above
example.

Cheers,
Mandeep

 

[Mandeep Dhami]

Sorry David did not get any help from the said web site. Probably I may have
not searched properly.
Just to explain further my requirement is as follows:
Column A
Name of City
New York
London
New York
New York
London

In this example New York is repeated 3 times and London is repeated 2 times.
I want a seperate list showing various cities in column A as shown in above
example repeated n number of times.

Cheers,
Mandeep

You could filter using unique records see
http://www.contextures.com/tiptech.html
advanced filter, unique records
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

Dear All,

Is there any formula by which we can count number of different data entered
in a column after filtiring the column.

I mean to say......if in column A......we enter names of various cities
around the world. Now when we filter by say New York we get column A with New
York......now I want to know how many different cities are entered in column
A.
For example there may be only 15 cities but they me be repeated 100 times in
column A.

Cheers,
Mandeep Dhami

 

[Bob Phillips]

That is not what you asked for.

B2: =A2
B3: =IF(ISERROR(MATCH(0,COUNTIF(B$2:B2,$A$2:$A$20&""),0)),"",
INDEX(IF(ISBLANK($A$2:$A$20),"",$A$2:$A$20),MATCH(0,COUNTIF($B$2:$B2,$A$2:$A
$20&""),0)))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter. Copy B3 down.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)