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

INDEX/MATCH Most Recent Entry
I am trying to pull from a sheet that is essentially a list of new records that are entered weekly. To keep things clean I have a helper sheet that just lists the project name once and the rest of the columns pull in through an INDEX/MATCH. I am trying to have that helper sheet pull in only the most recent entry. There is…
Break Up Multi-Entry Row
I'm using a form entry (into an Intake Sheet) in which someone can select multiple locations for the same Initiative. I'm looking to copy the row to a different Master sheet, but would like to have the same row copied multiple times, once per location selected. Intake Sheet: Desired Result in Master Sheet: Basically, I…
Set contents of two columns into 1
Hi - trying to figure out a formula that combines two columns into one long column, maintaining contents in multiple rows. Noting that the number of items in each column will change as these are each using the index/distinct to summarize a separate sheet. For example: Thanks for any help!