# Countifs using iferror or iserror

Options
edited 12/09/19

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!

Tags:

• ✭✭✭✭✭✭
Options

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

• Options

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?

• ✭✭✭✭✭✭
Options

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.

• Options

Thanks I'll give it a try!

Cheers

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!