Countifs using iferror or iserror

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!

Find more posts tagged with

Healthcare

Formulas

Support

Comments

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

Sign in to join the conversation!

Quick Links

Trending discussions

Is there a way to create a 3-digit padding for numbers
Hi all, and thanks in advance! I wanted to know if there was any way to create "padding" in Smartsheet. Basically I have account numbers, and a handful are still 2-digit, so they appear as TDL-17, TDL-67, etc. Most of the accounts are 3-digit numbers like TDL-180. I want something that will recognize whether it is 2 or 3…
Dynamic formula to return date of next Thursday every week.
I'm wondering if anyone has worked out a dynamic formula to return the date of the 'next' Thursday of the month. I have a worksheet where I need to send out a reminder to a contacts in a contact column in the worksheet each Thursday of the current month if a criteria has not been met. When criteria has been met, then…
Check box for date in previous month
Hello, I have the formula to check a box for line items with the current month. =IF(AND(YEAR(Date@row ) = YEAR(TODAY()), MONTH(Date@row ) = MONTH(TODAY())), 1, 0) Trying to set up a report for all items with the previous month (i.e. 1 month prior to current). How can I modify to check the box?