errors

hello

i am trying to use the COUNTIF function, where the range & criterion are the results of a VLOOKUP from another sheet.

When the VLOOKUP doesn't find what it is looking for, it returns a #NO MATCH Error and this in turn gives a #NO MATCH error in my COUNTIF.

On the VLOOKUP it is looking up an activity against a range of people, some who have got the activity against them, thereby giving a satisfactory result on the lookup, but those who haven't got the activity against them throw up a NO MATCH, which is what is screwing up my COUNTIF....

Find more posts tagged with

Comments

Mike Wilday

Try adding surrounding the search with an iferror. I did something similar here when looking at a list of dates...

=COUNTIFS([Upcoming Launch Date]:[Upcoming Launch Date], IFERROR(MONTH(@cell), 0) = 1, [Upcoming Launch Date]:[Upcoming Launch Date], IFERROR(Year(@cell), 0) = 2019)

The part where you determine the match should be surrounded by an IFerror... This will return a 0 instead of an error and skip that count. Share your actual formula and I will try to get it working for you if this doesn't put you on the right path.

joe78361

Thanks Mike

will look at this a bit later!

JOE

Mike Wilday

Sounds good. Feel free chime in here if you have further questions.

joe taylor

Hello Mike

Thanks, the IFERROR worked a treat.

My next challenge is - i want to use conditional formatting to tell me when an activity hasn't been carried out after say, 60 days. i am quite at home with CF, but as the column i want to format is the result of my VLOOKUP (with IFERROR added!), CF wont recognise it as a date type column, therefore nothing happens

If i manually type the date in, ie, overwrite the lookup, providing it meets the criteria, then ping, my cell turns orange...

joe taylor

Just wondering if anyone has any ideas for my post as above...

Mike Wilday

Sorry Joe. Can you post a screenshot of a cell with the formatting applied and a cell where the vlookup is providing the data and its not formatting it?

Sign in to join the conversation!

Quick Links

Trending discussions

Document filtered by form responses
I've created a Smartsheets form. When someone submits the form, I want them to automatically be emailed a document that has resources and links, but the document is filtered to only show the content that corresponds with what they selected in the form. Is something like this possible? What types of documents or automations…
Form stopped working after upgrade
I have a sheet that I created in a previous version, and it had a form. I was recently upgraded to a newer version and now my form is not working. I created a new form for the sheet, and it is giving me an error also. Does anyone know what I can do to fix this? The error says Form Unavailable. Sorry, the form you're trying…
Getting Error About Emulated Environments
Hi, I have read the post from 8/21/25. I am still getting the same error. I am setting up a new device and just download the latest version as of 9/5/25. I am using a Eunivon Smart White Board for touchscreen capability. It is running Android 14. Any help would be appreciated.