Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Occurences
I received the following formula which counts the number of unique
occurences within my Range "Range1" but I'm getting a #N/A error, yet when I go into the formula edit, it shows me a result of 16, which is correct Why would this be (BTW it has worked correcly for 8 weeks) {=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,R ange1,0),""),IF(LEN(Range1)0,MATCH(Range1,Range1, 0),""))0,1))} |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Occurences
Have you edited it and not array edited it? In other words, did you use
Ctrl-Shift-Enter? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... I received the following formula which counts the number of unique occurences within my Range "Range1" but I'm getting a #N/A error, yet when I go into the formula edit, it shows me a result of 16, which is correct Why would this be (BTW it has worked correcly for 8 weeks) {=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,R ange1,0),""),IF(LEN(Range1)0,MATCH(Range1,Range1, 0),""))0,1))} |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Occurences
This is an array formula, so you need to commit it with CTRL-SHIFT-
ENTER when you type it in or subsequently edit it. You should not type the curly brackets around the formula yourself. Another thing is to check that the data has a consistent format. Hope this helps. Pete On Mar 19, 10:54 am, "Sean" wrote: I received the following formula which counts the number of unique occurences within my Range "Range1" but I'm getting a #N/A error, yet when I go into the formula edit, it shows me a result of 16, which is correct Why would this be (BTW it has worked correcly for 8 weeks) {=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,R ange1,0),""),IF(LEN(Range*1)0,MATCH(Range1,Range1 ,0),""))0,1))} |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Occurences
On Mar 19, 11:20 am, "Bob Phillips" wrote:
Have you edited it and not array edited it? In other words, did you use Ctrl-Shift-Enter? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... I received the following formula which counts the number of unique occurences within my Range "Range1" but I'm getting a #N/A error, yet when I go into the formula edit, it shows me a result of 16, which is correct Why would this be (BTW it has worked correcly for 8 weeks) {=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,R ange1,0),""),IF(LEN(Range*1)0,MATCH(Range1,Range1 ,0),""))0,1))}- Hide quoted text - - Show quoted text - Nope, didn't edit it and yes I committed with Ctrl-Shift |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Count Unique Occurences
On Mar 19, 11:20 am, "Bob Phillips" wrote:
Have you edited it and not array edited it? In other words, did you use Ctrl-Shift-Enter? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Sean" wrote in message ups.com... I received the following formula which counts the number of unique occurences within my Range "Range1" but I'm getting a #N/A error, yet when I go into the formula edit, it shows me a result of 16, which is correct Why would this be (BTW it has worked correcly for 8 weeks) {=SUM(IF(FREQUENCY(IF(LEN(Range1)0,MATCH(Range1,R ange1,0),""),IF(LEN(Range*1)0,MATCH(Range1,Range1 ,0),""))0,1))}- Hide quoted text - - Show quoted text - Its working now! I had changed some data (which was referenced to another workbook) and that is where my #N/A appeared. Although it was linked correctly, my Count formula only worked when I closed the workbook and re-opened it again. Is that strange? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I count the # of unique occurences of a text in a column? | Excel Worksheet Functions | |||
Count # of Occurences | Excel Discussion (Misc queries) | |||
List Unique Occurences and Count | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions | |||
unique occurences in list | Excel Worksheet Functions |