Calculating the number of unique values within a large range
Hi,
I don't think the formula you entered will work.
If there are no blank cells in the range you can use
=SUM(1/COUNTIF(B2:B65536,B2:B65536))
entered as an array; or without an array:
=SUMPRODUCT(1/COUNTIF(B2:B65536,B2:B65536))
These function are very slow when going against 65,000 row.
Here is one formula which takes blanks into account:
=SUM(1/(IF(COUNTIF(B2:B65536,B2:B65536)0,COUNTIF(B2:B655 36,B2:B65536),1)))-(COUNTBLANK(B2:B65536)0)
This is array entered. if you know there are blanks you can just replace
(COUNTBLANK(B2:B65536)0) with 1.
--
Thanks,
Shane Devenshire
"SiH23" wrote:
I would be most grateful if someone could offer me some help.
I need to count the number of unique values within a column range. The range
is between B2:B65536. Each value consists of a series of alpha and numerical
characters and there are blank cells within the range.
There will be around 30,000 plus entries.
I have used various formulas, but due to the vast number of rows of data
they crash.
An example of one of the formulas I used is below:
=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))
I am desperate for an answer but am confused as to how to do this? Any help
or assistance would be very much greatly appreciated.
|