COUNTIFS with vLookup Results

When i reference the VLOOKUP fields in the COUNTIFS formula, I get this error #NO MATCH.

=COUNTIFS([Server Install Status]:[Server Install Status], "Installed", [In Scope]:[In Scope], "Yes")

The [Server Install Status] column is populated with the results of =VLOOKUP([Server Name]1, {cmdb_ci_win_server20200810 Range 1}, 3, false). The results in the column are correct. However, when I reference this data in the COUNTIFS formula, I get the error above.

Please advise.


Best Answers


  • There are a few rows with #NO MATCH. Is there a way to resolve this in the formula?

  • I tried to use the IFERROR formula with it, but it doesn't render the count I expect. It just returned a blank, not the total number of rows with data.

    =IFERROR(COUNTIFS([Server Install Status]:[Server Install Status], "Installed", [In Scope]:[In Scope], "Yes"), " ")

    More background in case it helps with suggestions...   My project tracking sheet has rows of data with server names, if the server is in scope and what the install status is of the server (i.e. Installed, Retired, etc.). Instead of looking up each server status in the CMDB individually, I 

    1. Create a report from the CMDB with the server names and their respective install statuses,
    2. Import the CMDB report into Smartsheet,
    3. Use the vLookup formula to update the install status on my primary tracking sheet

    It is very likely that a couple of the servers on the CMDB <> have a match in the primary tracking sheet, thus the #NO MATCH result.

    Since, I am working with server names, and can not share that information here.

  • Thanks @David Joyeuse , @MCorbin,

    I was able to use

    =IFERROR(VLOOKUP([Server Name]4060, {NEW SPREADSHEET Range 1}, 3, false), (VLOOKUP([Server Name]4060, {ORIGINAL SPREADSHEET Range 1}, 4, false))) to prevent the #NO MATCH error. Then, I was able to use

    =COUNT(DISTINCT(COLLECT([Server Name]:[Server Name], [In Scope]:[In Scope], @cell <> "No", [Server Install Status]:[Server Install Status], @cell = "Installed")))

    … to obtain my goal of the count of DISTINCT, IN SCOPE, INSTALLED server names.

    I appreciate all of your help! 😎

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!