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))}

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))}

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))}

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

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?