Countif to ignore any errors
 

I would like to count the number of records which have good data , ignoring
the one's that have any errors

for eg: the data in cell A1:a6 is as follows:-

aa
bb
#NUM!
cc
#NUM!
dd

The count should give me 4 as only 4 out of the 6 above holds correct data

I cannot change the formula that is causing the error (like #NUM! above
using ISERROR route)

I am using this array formula but not able to get the desired result.

{=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")}

Any suggestions please

Thanks

Try

=SUMPRODUCT(--NOT(ISERROR(A1:A6)))


<SD wrote in message ...
I would like to count the number of records which have good data ,
ignoring
the one's that have any errors

for eg: the data in cell A1:a6 is as follows:-

aa
bb
#NUM!
cc
#NUM!
dd

The count should give me 4 as only 4 out of the 6 above holds correct data

I cannot change the formula that is causing the error (like #NUM! above
using ISERROR route)

I am using this array formula but not able to get the desired result.

{=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")}

Any suggestions please

Thanks

You could try either:
=COUNTIF(A1:A6,"<#NUM!")
=SUM(IF(ISERR(A1:A6),0,1))

Art

"SD" wrote:

I would like to count the number of records which have good data , ignoring
the one's that have any errors

for eg: the data in cell A1:a6 is as follows:-

aa
bb
#NUM!
cc
#NUM!
dd

The count should give me 4 as only 4 out of the 6 above holds correct data

I cannot change the formula that is causing the error (like #NUM! above
using ISERROR route)

I am using this array formula but not able to get the desired result.

{=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")}

Any suggestions please

Thanks

This is an alternative, but isn't as good because it doesn't count numbers
unless they are formatted as text (and reentered) or entered as text.

=COUNTIF(A1:A6,"*")


<SD wrote in message ...
I would like to count the number of records which have good data ,
ignoring
the one's that have any errors

for eg: the data in cell A1:a6 is as follows:-

aa
bb
#NUM!
cc
#NUM!
dd

The count should give me 4 as only 4 out of the 6 above holds correct data

I cannot change the formula that is causing the error (like #NUM! above
using ISERROR route)

I am using this array formula but not able to get the desired result.

{=COUNTIF(IF(ISERROR(A1:A6)=FALSE,D57:D66),"<FALS E")}

Any suggestions please

Thanks

Dave R. wrote...
This is an alternative, but isn't as good because it doesn't count
numbers
unless they are formatted as text (and reentered) or entered as text.

=COUNTIF(A1:A6,"*")
....

There are times 2 functions make more sense than one.

=COUNTIF(range,"*")+COUNT(range)
Both function calls skip cells evaluating as error or boolean values.