Count no. of different entries in column

[j844929]

Hi,

I have numerous columns that I would like to analyse, but can't seem to get the correct calculation.

For instance, column A contains company numbers, ie. :118,119,132,145,157

I was just wondering whether someone could give me a calculation similar to a =countif() or =count() that would return "5", telling me there are 5 different numbers in column A...? The other thing is that it has to work for text as well...

Any ideas would be gratefully received...

Tim

 

[fairwinds]

Hi,

Try:

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

 

[j844929]

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?

 

[fairwinds]

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

 

[j844929]

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

 

[fairwinds]

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.

 

[j844929]

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.

 

[fairwinds]

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

 

[j844929]

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.

 

[fairwinds]

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