Count no. of different entries in column

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 30, 2004

• #2

Hi,

Try:

=SUMPRODUCT((A1:A10<>"")/COUNTIF(A1:A10,A1:A10&""))

 

Upvote 0

j844929

Active Member

Joined

Aug 18, 2002

Messages

423

• Apr 30, 2004

• #3

Seems to work if there is only one instance of the number, but each company number appears in the column about 200 times.....

Is there another way to do it?

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 30, 2004

• #4

Is not this what you want?

Book1
	A	B	C	D
1	aaa	5
2	aaa
3	bbb
4	ccc
5	aaa
6	ddd
7	eee
8	eee
9
10
Sheet1

 

Upvote 0

j844929

Active Member

Joined

Aug 18, 2002

Messages

423

• Apr 30, 2004

• #5

It is, but it isn't...

I've figured out what the problem is. I don't know how many rows of data I'm going to have, but it will be less than 10,000; thus when I entered the function, I wanted to analyse from row A2:A9999, this caused a #DIV/0! problem.

It did, however, work when I analysed only 2157 rows of data that I currently have...

I was just wondering if there was a way to build this other factor into the function.....

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 30, 2004

• #6

Sorry but I cannot see what is causing your problem, blank cells in the range should not cause problems.

Only issue I can see is that it is an expensive formula and could take a while to calculate on a large range.

A Pivot table can be en alternative.

 

Upvote 0

j844929

Active Member

Joined

Aug 18, 2002

Messages

423

• Apr 30, 2004

• #7

I was beginning to think along the lines of pivot table as well. The function works well enough to use it, however. I'll just have to change the formula when more data is added.

Thanks.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 30, 2004

• #8

... still dont get why you cannot have the whole range from the start. Is there something in the cells or are they blank?

 

Upvote 0

j844929

Active Member

Joined

Aug 18, 2002

Messages

423

• Apr 30, 2004

• #9

They're blank. It's a really strange one, I just can't figure it out...

The information is in a sheet called AggregatedData, and I wrote the function thus:

=SUMPRODUCT((AggregatedData!$A$2:$A$9999<>"")/COUNTIF(AggregatedData!$A$2:$A$9999,AggregatedData!$A$2:$A$9999&""))

But this didn't work... Still can't figure why.

 

Upvote 0

fairwinds

MrExcel MVP

Joined

May 15, 2003

Messages

8,638

• Apr 30, 2004

• #10

Your formula looks perfectly OK and worked for me as soon as I pasted it in my sheet.

 

Upvote 0