Counting the number of unique values within a range
 

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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.

Counting the number of unique values within a range
 

Not sure why it would crash, but you do have an extra ) at the end. It worked
fine for me...

"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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.

Counting the number of unique values within a range
 

There is nothing wrong with the formula - it worked on my worksheet
Save you file, reopen and try again
It this fails I suspect a corrupt file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SiH23" wrote in message
...
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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.

Counting the number of unique values within a range
 

Many thanks for all your help - I forgot to say that some of the cells within
the column are blank. Do I need to alter the formula in anyway? I suspect
this maybe why I am having problems.

"Bernard Liengme" wrote:

There is nothing wrong with the formula - it worked on my worksheet
Save you file, reopen and try again
It this fails I suspect a corrupt file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SiH23" wrote in message
...
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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.

Counting the number of unique values within a range
 

Many thanks for all your help - I forgot to say that some of the cells within
the column are blank. Do I need to alter the formula in anyway? I suspect
this maybe why I am having problems.

"Sean Timmons" wrote:

Not sure why it would crash, but you do have an extra ) at the end. It worked
fine for me...

"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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.

Counting the number of unique values within a range
 

Try it like this:

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

About you file crashing...

That formula is very calculation intensive. If your real range is "large"
(1000 rows) it will be slow to calculate. If your range is really just 5
rows then you should have no problem.

--
Biff
Microsoft Excel MVP


"SiH23" wrote in message
...
Many thanks for all your help - I forgot to say that some of the cells
within
the column are blank. Do I need to alter the formula in anyway? I suspect
this maybe why I am having problems.

"Bernard Liengme" wrote:

There is nothing wrong with the formula - it worked on my worksheet
Save you file, reopen and try again
It this fails I suspect a corrupt file
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

"SiH23" wrote in message
...
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. Each
value consists of a series of alpha and numberical characters.

I have used the formula below but it keeps crashing my spreadsheet:

=SUMPRODUCT((1/COUNTIF(A1:A5,A1:A5&"")))

Any help or advice would be greatly appreciated.