Countifs using iferror or iserror

Tiffany White

Hello,

I'm trying to use a COUNTIFS formula to count the number of VLOOKUP errors in another sheet that occur in the past month. From what I can see in other discussions the IFERROR and ISERROR functions are generally used to give a 0 value but I want it to return a value of 1 for each error. This is the formula I've been trying and I keep getting a total of 0 but I manually counted the errors and there were 15. I'm sure it's something silly that I'm missing and I would really appreciate any help or advise on how to get this to work.

=COUNTIFS({Weekly Report Range 1}, 1, {Weekly Report Range 2}, IFERROR({Weekly Report Range 2}, 1))

Thank you!

Paul Newcome

What is in the two different ranges? When using a COUNTIFS, it is essentially saying to count if ALL criteria is met, so if  range 1 AND range 2 do not both equal 1, then you will get a result of 0. It is also helpful to use @cell references in your criteria.

 

@cell = 1

Tiffany White

The first range is to determine if the visit was within the past month and the second is to determine the customer. What I would like to do is count all visits within the last 30 days that come up with an error when referencing our client list (in other words I want to count all the visits to potential clients - anyone who isn't currently on our client list).

I'm referencing a separate sheet which is why I was using ranges. Can I use @cell when using VLOOKUP on another sheet?

Paul Newcome

Are both ranges checkboxes or some other kind of true/false flag?

 

If so, your current formula should work if you use @cell = 1 for your criteria and if you are using the appropriate steps for referencing another sheet.

Tiffany White

Thanks I'll give it a try!

Cheers