Counting Unique Values - #N/A Error 4

[mrsnrub]

Hello everyone,
I am using an array formula in Excel 2003 to count the unique values in a range.

=SUM(IF(FREQUENCY(IF(LEN(D3:BP3)>0,MATCH(D3:BP3,D3:BP3,0),""), IF(LEN(D3:BP3)>0,MATCH(D3:BP3,D3:BP3,0),""))>0,1,0))

This formula has come from the help.

I am receiving the #N/A value when it evaluates, and going through the evaluation steps is not making things any clearer for me.

The data in the range is all produced using formulas, and I have found that if I copy and paste values (removing the formulas) the array formula produces the expected result.

Does anyone know why this may be happening? Is there a problem using array formulas when you are evaluating formulas values rather than straight text values?

Any help would be greatly appreciated.

 

[KenWright]

Limited testing but it appears to crash every time you introduce a volatile function such as AREAS(), INDEX(), OFFSET(), CELL(), INDIRECT(), ROWS(), COLUMNS(), NOW(), TODAY(), RAND() etc

Non-volatile functions seem to work fine, but volatile appear to be a no no.

Regards
Ken............



----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mrsnrub]

Thanks for the reply Ken - very interesting, I have learnt something today.

I was using INDIRECT.

I've managed to get around this by using a user defined function.

Cheers,
Steve

 

[KenWright]

You're very welcome, and I learned something as well

Cheers
Ken............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[GlennUK]

Yes, well done Ken

===> *



Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[KenWright]

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[xlhelp]

Tek-Tips ist wunderbar.

I learnt something new as well. Thanks, Ken

Member AAA - Abolish Abused Abbreviations

 

[MSIsam]

I have been using this array formula, which seems to work for me.:

=SUM(IF(COUNTIF(A1:D10,A1:D10)=0, "", 1/COUNTIF(A1:D10,A1:D10)))

 

[firefytr]

Ken, I had that problem months ago and submitted it as a bug to Mike F. I was under the impression that it was resolved in one of the last updates though. What version/SP are you using? 2003 I'm assuming..

-----------
Regards,
Zack Barresse

 

[KenWright]

2003 SP1 so I might be out of date?

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[firefytr]

I would think so, but I'm not 100% sure either. I know a good way to find out.

-----------
Regards,
Zack Barresse

 

[KenWright]

I ran it the other day but it kept prompting me for the damn installation discs and I just couldn't be bothered to go trawl through my MSDN collection and find the right ones, so I just skipped the update.

Regards
Ken...........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------