errors

joe taylor

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....

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?