COUNTIF not containing #N/A

[bines53]

Hello friends,

Does anyone know a way to avoid calculating #N/A cells?

=SUM(INDEX(COUNTIF(B1:B11,D1:D11&"<>#N/A"),0)) ,does not work ,

The correct result is 12

Thanks for the helpers

David

 

[Fluff13]

How about
=SUM(COUNTIFS(B1:B11,D1:D11,B1:B11,"<>#N/A"))

 

[bines53]

Hi @Fluff13 ,

I know that, it's possible too =SUM(INDEX(COUNTIF(B1:B11 ,IFERROR(D1:D11 ," ")),0)) or with IFNA.

Thank you

 

[Fluff13]

I know that,

Then why did you ask?

 

[bines53]

Hi @Fluff13 ,

To know where the mistake in the construction of &"<>#N/A"

 

[Fluff13]

There is no mistake.

 

[Rickbale]

Hello friends,

Does anyone know a way to avoid calculating #N/A cells?

=SUM(INDEX(COUNTIF(B1:B11,D1:D11&"<>#N/A"),0)) ,does not work ,

The correct result is 12

Thanks for the helpers

David

Try this formula

=COUNTA(Z:Z)-SUM(IF(ISNA(Z:Z),1))
=SUMPRODUCT(--(TRIM(Z:Z)<>"#N/A"))

Regards,
Rick Bale