Calculating the number of unique values with a range

S

SiH23

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

muddan madhu

try this

=SUM(1/COUNTIF(B2:B65636,B2:B65636)) ( array function use Ctrl +
Shift + Enter )
 
T

T. Valko

Have you ever tried that formula on that big of a range, and if so, how long
did it take to calculate (if it ever did!) ?

--
Biff
Microsoft Excel MVP


try this

=SUM(1/COUNTIF(B2:B65636,B2:B65636)) ( array function use Ctrl +
Shift + Enter )
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Counting the number of unique values within a range 5
Calculating the number of unique values within a large range. 5
Calculating the number of unique values within a large range 3
Count of unique values 8
Return Just the Unique Entries From a Range of Cells 3
calculate values in an unlimited range of cells in a column WITHOUTalso calculating values that had 2
How to count the number of unique entries in a filtered column 8
Conditional format for unique values 5

Top