Countif (Counting w/out counting duplicates)

pzamory · May 14th, 2002, 07:51 AM

Hello. I have a long row of names, most of them repeated several times. I would like to get a count of how many names appear (but I only want to count each name once).

Smith
Smith
Smith
Jones
Anderson

Peter Z.

Audiojoe · May 14th, 2002, 07:55 AM

You could use a filter? Just click DATA>FILTER>AUTO FILTER then you can pretty much manipulate it any way you want

pzamory · May 14th, 2002, 07:56 AM

There are over 800 names and with the filter, I would still have to manually count the number of names. Thanks anyway.... I appreciate it.

pz

pzamory · May 14th, 2002, 08:11 AM

I found the answer in an earlier post. Filter, Advanced Filter and put the results in another column (with no duplicates), then simply do a "count" or "counta".

Thanks.

sen_edp · May 14th, 2002, 08:16 AM

Hello pzamory
take a look at this
it might help without autofilter

Microsoft Excel - HtmlMaker.xls
File(F) Edit(E) View(V) Insert(I) Tool(T) Data(D) Window(W) Help(H)
I7		=
	A	B	C	D	E	F	G
1	andreas		:alert('1')>=COUNTIF(Data,Data)	:alert('1')>=1/C1:C12
2	jim		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
3	jim		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
4	bill		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
5	bill		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
6	ted		:alert('1')>=COUNTIF(Data,Data)	:alert('1')>=1/C1:C12
7	frans		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
8	frans		:alert('2')>=COUNTIF(Data,Data)	:alert('0,5')>=1/C1:C12
9	gus		:alert('4')>=COUNTIF(Data,Data)	:alert('0,25')>=1/C1:C12
10	gus		:alert('4')>=COUNTIF(Data,Data)	:alert('0,25')>=1/C1:C12
11	gus		:alert('4')>=COUNTIF(Data,Data)	:alert('0,25')>=1/C1:C12
12	gus		:alert('4')>=COUNTIF(Data,Data)	:alert('0,25')>=1/C1:C12
13				:alert('6')>=SUM(D1:D12)	<-- Unique items in Column A
14
15				:alert('6')>=SUM(1/COUNTIF(Data,Data))	<-- Single formula
16
Sheet1

You can see the value of cells only click each above hyperlinks

range data is A1:A12
_________________
Best Regards,
Andreas
using Excel 2000 - Windows 2000 prof.

[ This Message was edited by: sen_edp on 2002-05-14 07:18 ]

Ian Mac · May 14th, 2002, 08:21 AM

Quote:

On 2002-05-14 07:11, pzamory wrote:
I found the answer in an earlier post. Filter, Advanced Filter and put the results in another column (with no duplicates), then simply do a "count" or "counta".

Thanks.

try:

http://www.mrexcel.com/board/viewtop...c=3047&forum=2

this has the formula AND explaination you should need

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

May 14th, 2002, 07:51 AM	#1
pzamory Board Regular Join Date: May 2002 Posts: 98	Hello. I have a long row of names, most of them repeated several times. I would like to get a count of how many names appear (but I only want to count each name once). Smith Smith Smith Jones Anderson Peter Z.

May 14th, 2002, 07:55 AM	#2
Audiojoe Board Regular Join Date: Feb 2002 Location: Where the wild roses grow Posts: 285	You could use a filter? Just click DATA>FILTER>AUTO FILTER then you can pretty much manipulate it any way you want

May 14th, 2002, 07:56 AM	#3
pzamory Board Regular Join Date: May 2002 Posts: 98	There are over 800 names and with the filter, I would still have to manually count the number of names. Thanks anyway.... I appreciate it. pz

May 14th, 2002, 08:11 AM	#4
pzamory Board Regular Join Date: May 2002 Posts: 98	I found the answer in an earlier post. Filter, Advanced Filter and put the results in another column (with no duplicates), then simply do a "count" or "counta". Thanks.