Recent content by AllanB1

Deniall: Yes thank you. I was also successful in using this variant in all the examples in the snippet. =SUMPRODUCT( --(ISERROR(A:A)) )

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...

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...

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?

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.

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...

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...

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...

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.

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*")...

Great. Thanks Skip.

Won't allow me to group that selection. It's in number format.

Anyone know of a way to group data by number ranges (1-10, 11-20,...)? I know I can come up with something in a usual spreadsheet, but does the pivot table offer any way like it does with dates, ie., by week? For example, I need to know an average cost by vendor for 1-10 miles, etc. Thanks.

Mike/Olaf thanks for further expounding on this subject. I have never seen much difference between the two myself, but thought I'd put this out for public discussion. Thanks. Allan

Olaf, thank you for your excellent explanation. I can't see much difference either, except that you are unable to edit objects contained within a control class, but I don't consider that an advantage. Allan