how to make formula not say #N/A

[MetteGaga]

Hi,

I have this formula in one of my templates.
=SUM(FREQUENCY(MATCH(A6:A999;A6:A999;0);MATCH(A6:A999;A6:A999;0))>0;1)

But some times i dont have data in all the Rows.. then the formula says #N/A and i have to manuel change the formula..
Is there any way that i can make the formula ignore the blancks and just make the calculation on the rows that have data in them?

thanks in adwance...

[:) Sixthsense :)]

May be this?

=SUM(FREQUENCY(IFERROR(MATCH(A6:A999,A6:A999,0),0),IFERROR(MATCH(A6:A999,A6:A999,0),0))>0,1)

[Tony Valko]

Try it like this...

Array entered**:

=SUM(IF(FREQUENCY(IF(A6:A999<>"";MATCH(A6:A999;A6:A999;0));ROW(A6:A999)-ROW(A6)+1);1))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

[MetteGaga]

when i do the Ctrl, Shift and then enter the formula says #REF!

[MetteGaga]

Sorry i maid a mistake it works. thanks a lot

[Tony Valko]

Good deal. Thanks for the feedback!

If your question has been solved please mark the thread as being solved.

In the menu bar above the very first post select Thread Tools, then select Mark this thread as solved.