COUNTIF ?

[coccio]

Hi,

I have a formula below:

=INDEX('6707'!$B$1:$B$600,MATCH($A$1,'6707'!$A$1:$A$600,0)*1)

Which works fine for me but when there is no data the cell displays a #N/A

I’m trying to do the COUNTIF function but can’t seem to get it right.

How do I do remove the #N/A to above formula?

 

[Aladin Akyurek]

On 2002-10-23 09:19, coccio wrote:
Hi,

I have a formula below:

=INDEX('6707'!$B$1:$B$600,MATCH($A$1,'6707'!$A$1:$A$600,0)*1)

Which works fine for me but when there is no data the cell displays a #N/A

I’m trying to do the COUNTIF function but can’t seem to get it right.

How do I do remove the #N/A to above formula?

Try:

=IF(ISNUMBER(MATCH($A$1,'6707'!$A$1:$A$600,0)),INDEX('6707'!$B$1:$B$600,MATCH($A$1,'6707'!$A$1:$A$600,0)),"ValueToReturnInsteadOf#N/A")

If you have the morefunc add-in, you can use:

=IF(ISNA(SETV(INDEX('6707'!$B$1:$B$600,MATCH($A$1,'6707'$A$1:$A$600,0)))),"ValueToReturnInsteadOf#N/A",GETV())

Addendum. Also an attractive approach...

In B1 enter:

=MATCH($A$1,'6707'$A$1:$A$600,0)

In C1 enter:

=IF(ISNUMBER(B1),INDEX('6707'!$B$1:$B$600,B1),ValueToReturnInsteadOf#N/A")
This message was edited by Aladin Akyurek on 2002-10-23 09:28

 

[coccio]

WOW
Fantastic

Thanks