Specify Range Function 3

[AllanB1]

Hi. I am looking for a way to make the ISERROR function work over a range. I have tried the following to no avail:

=COUNTIFS($G:$G,"=ISERROR()",$C:$C,"*PREVENTIVE MAINT*")

=COUNTIFS($G:$G,"=ISERROR(G:G)",$C:$C,"*PREVENTIVE MAINT*")

=COUNTIFS($G:$G,"=ISERROR(INDIRECT(""G""&ROW()))",$C:$C,"*PREVENTIVE MAINT*")

I know I can use a workaround such as creating an adjacent "test cell" in each row and work with that. But I was looking for the correct syntax in using a modification of the above.

Thank you.

Allan

 

[Deniall]

It was the #NUM error I was specifically seeking in your "snippet":[ ] I cannot properly investigate a behaviour that I do not have.

I downloaded your snippet, and (on my computer at least) it behaves exactly as I would expect.[ ] If you are puzzled by the zero results that I am getting and I suspect you are also getting, then you didn't take in the significance of the final parenthesised sentence of my 1Mar15@20:24 post.

I am uploading a very minor variant of your snippet, corrected to eliminate your erroneous zero results, and expanded to cover both the bounded and the unbounded approaches to specifying the ranges.[ ] On my computer this file is working EXACTLY as I would expect.[ ] If you are still experiencing the #NUM problem, please post a small example spreadsheet that exhibits the problem.

 

[AllanB1]

Deniall:

Yes thank you. I was also successful in using this variant in all the examples in the snippet.

[tt]=SUMPRODUCT( --(ISERROR(A:A)) )[/tt]

 

[Deniall]

I'm glad you've got things working.
(Even if we do not seem to have worked out why you were getting the #NUM error at one stage.)