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

 

[SkipVought]

Hi,

???

You want to count error in column G?

I'm not getting what you're trying to do.

 

[AllanB1]

Sorry, I should have explained.

Using COUNTIFS(), I'm trying to compare the contents of cells in columns C and G. If there is an error in G cell AND C cell contains PREVENTIVE MAINT, I want the count of those rows meeting that condition.

Thanks.

 

[SkipVought]

Cells with errors are a problem that should be fixed BEFORE performing any aggregations.

You should not be testing for error but rather fixing errors. Your formula is suspect.

 

[AllanB1]

I realize that. My question is more of a general one though and I used ISERROR() as an example.

Essentially, is there any range parameter I can use - i.e., between the ISERROR(??) parentheses - to make an aggregate function perform?

BTW, the 3 examples I provided in my initial message don't produce an error themselves, but they produce a result of 0.

I realize there may not be any way to do what I am suggesting within this (or any) aggregate function.

Other functions could be ISTEXT(), ISNA(), ISEVEN()... And I know there are other ways besides using these with a COUNTIF or COUNTIFS function to achieve the result.

My most basic question though is what can I put in here to make this (and others)aggregate function successful? Is this even possible?

=COUNTIF($G:$G,"=ISERROR(???)")

 

[SkipVought]

If you have any error in your arrogation range, then TILT! Nothing "works"!

Sometimes you might get an error in a lookup, where the lookup value does not exist in the lookup range. This can be masked using the IFERROR() function like...
[tt]
=IFERROR(SomeLookupFunction, "Return This Text Instead")
[/tt]
So my question to you is, 1) what is the formula returning an error, 2) what SHOULD be returned in these error instances?

 

[AllanB1]

Skip:

I'm sorry I didn't explain my intent that well. I was using the ISERROR() function only as one example of what I was trying to achieve.

While I realize there are other ways to do what the following formula shows, my question is, can I achieve my desired result using a function like ISNUMBER() in an aggregate function like COUNTIF()?

=COUNTIF($G:$G,"=ISNUMBER()")

In the above case, I would like to indicate the number of cells containing numbers in column G. The above produces a result of 0, when there are numbers in the G column.

Again, I know there are other ways to achieve what I'm asking, but what would be the correct syntax in my example above? Is this even possible?

Thanks.

 

[SkipVought]

If you mean that column G contains BOTH numbers and text then my question to you is what kind of table are you processing? Numbers and text do not belong in the same category and a table contains columns where each individual column is a category. So you'd never have a column with the NAMES of fruit and the WEIGHT of the fruit picker.

 

[Deniall]

Try a variant of
=SUMPRODUCT([ ] (ISERROR($G:$G))[ ] *[ ] ($C:$C="PREVENTIVE MAINT")[ ] )
where I have added some space characters to help clarify what is going on.

This approach relies on each of the multiplicands that form part of the single argument to the SUMPRODUCT function being logical expressions.[ ] They therefore return TRUE or FALSE results, and then the multiplication operator (*) forces these results to be converted to 1 or 0 respectively.

This can probably be extended to accommodate your wildcards ("*PROTECTIVE MAINT*"), perhaps using the SEARCH function.[ ] I haven't tested this, because I didn't want to deprive you of the fun involved.

 

[AllanB1]

Deniall:

I was unsuccessful in making your suggestion work although I attempted several variations. That's not to say to it couldn't work, but I was unable to.

In lieu of making this work, I simply employed an additional logical test column H and used this successfully:

=COUNTIFS($H:$H,"=TRUE",$C:$C,"*PREVENTIVE MAINT*")​

I realized I could do this initially, but was seeking a method without using an additional column. What I was originally looking for was a way to use an IS function, and used ISERROR() as an example. I believe the problem is that IS functions of course require a cell reference inside the parentheses [tt]=COUNTIFS($G:$G,"=ISERROR([highlight #FCAF3E]G:G[/highlight])",$C:$C,"*PREVENTIVE MAINT*")[/tt], and this may be unsupported within the aggregate function COUNTIFS() inside the quotes.

There may still be a solution though.

Thank you and Skip.

 

[SkipVought]

I tries this for a column with a mixture of numbers and text
[tt]
=SUMPRODUCT(ISNUMBER(A:A)*(B:B="skip"))
[/tt]

This worked for an error in a column
[tt]
=SUMPRODUCT(ISERROR(A:A)*(B:B="skip"))
[/tt]

FYI, I find the SUMPRODUCT() function, using the product of each expression rather than the list of expressions, much more versatile than using COUNTIFS or SUMIFS.

 

[Deniall]

There was a bit more fun in it still.[ ] This seems to work.
=SUMPRODUCT([ ] (ISERROR($G:$G))[ ] *[ ] (NOT(ISERROR(SEARCH("PREVENTIVE MAINT",$C:$C))))[ ] )

And I heartily endorse Skip's comment about the versatility of the SUMPRODUCT function.[ ] It has uses for all sorts of non-obvious purposes.

 

[AllanB1]

Skip/Deniall:

The SUMPRODUCT() variations you presented work well for my purpose. And I agree, its versatility can be adapted for many purposes. I hadn't considered it before.

Thanks again.

 

[AllanB1]

Question, if I enter =SUMPRODUCT(ISERROR(A1:A65000) * (B1:B65000="PREVENTIVE")) (or any other start/end cell ranges), I obtain a correct result.

However, if I use =SUMPRODUCT(ISERROR(A:A) * (B:B="PREVENTIVE")) (column ranges), I get the #NUM error.

Any obvious explanation?

 

[SkipVought]

At some time earlier I was about to add a marginally related comment. Now might be a good time.

If you're developing a workbook, use Structured a Tables or Named Ranges. From the point of view of aesthetics, maintenance, understandably, it make sense as well as in this particular circumstance.

 

[Deniall]

No obvious explanation.[ ] No non-obvious explanation either, because I cannot reproduce your #NUM result.

The formula I give above (01Mar15@00:22) was cut&pasted directly out of the spreadsheet in which I created it, where it worked fine.[ ] I have just revisited that spreadsheet and removed the $ indicators from the range specifications.[ ] It still works fine.

For the record, I am using Excel 2010 under Windows-7 Professional.[ ] The formula work with Excel running in "native mode" and with it running in compatibility mode.

 

[Deniall]

A late thought.[ ] Try
=SUMPRODUCT( ISERROR(A1:A65000) )
and
=SUMPRODUCT( (B1:B65000="PREVENTIVE") )
separately, and see whether you get #NUM from both.[ ] If only one gives you the #NUM then it's a fair assumption that the problem does not lie with the column referencing syntax.

(To get results other than zero you might need to multiply the terms by 1.0 to force the conversion from logical to numeric, but this is not relevant to the test being performed, which is what happens to the #NUM.)

 

[AllanB1]

1. Skip, yes I agree that structured tables and named ranges are very effective tools in data organization. Are you saying that their use may in some way resolve the situation I presented in the preceding message?

2. Deniall, I also am using XL 2010, under Win7 Pro.

3. These 2 formulas you suggested,

=SUMPRODUCT( ISERROR(A1:A65000) )
and
=SUMPRODUCT( (B1:B65000="PREVENTIVE") )

each produce a 0 result when the columns are populated with data that should satisfy the logical conditions and produce sums greater than 0.

But when I changed the column ranges shown to A:A and B:B respectively, I obtained the #NUM error, as was my original problem.



.

 

[Deniall]

AllanB1,

That's seriously weird.[ ] In fact it was weird enough before your most recent response.[ ] It is now officially above my pay-grade. However if you can get your spreadsheet to me, the version that actually exhibits this behaviour, I'll feed it into my Excel and see if I get the same results.[ ] To get it to me, either post it as an attachment thru Tek-Tips, or you can get my e-mail address from my web site and you can get my web site from my Tek-Tips "member profile".

If you'd rather not send the spreadsheet itself (for confidentiality or any other reason), create yourself a simpler, cut-down version that still exhibits the problem and send that instead.[ ] Come to think of it, trying to reproduce the problem in a small, focussed spreadsheet might be a useful diagnostic exercise anyway.

 

[AllanB1]

Deniall:

Your pay grade and mine, my friend. Something so seemingly simple...

Anyway, I replicated my situation in a "snippet" sheet, link provided.

For some reason the #NUM error I brought up in my last message no longer occurs, don't even ask.