Thread: 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),"<>FALSE")}

Any suggestions please

Thanks

Try

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


<SD> wrote in message news:%23Y0Dy5N9EHA.3908@TK2MSFTNGP12.phx.gbl...
> 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),"<>FALSE")}
>
> 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),"<>FALSE")}
>
> 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 news:%23Y0Dy5N9EHA.3908@TK2MSFTNGP12.phx.gbl...
> 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),"<>FALSE")}
>
> 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.