| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Counting unique values in a row | New Users to Excel | |||
| Counting Unique Values by Date Range | Excel Worksheet Functions | |||
| Counting Unique Values | Excel Discussion (Misc queries) | |||
| Counting unique text/number cells from a range | Excel Discussion (Misc queries) | |||
| Counting unique text/number cells from a range | Excel Worksheet Functions | |||
Linear Mode
