joe taylor
edited 12/09/19 in Smartsheet Basics


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

  • Thanks Mike

    will look at this a bit later!


  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

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

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

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

  • Mike Wilday
    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?