COUNTIFS with vLookup Results

Lisa Blair

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.

MCorbin

If any value in your [Server Install Status] column has "#No Match", you will get an error in your formula that is looking in that column.

The formula is ok, it's the data that's causing your problem. Go back to your data, find the row that has "#No Match" and see if you can resolve the lookup error.

David Joyeuse

Hi Lisa,

#NO MATCH in a COUNTIFS happens when you have a #NO MATCH on your VLOOKUP. And you only need one occurence of a #NO MATCH in the VLOOKUP for the COUNTIFS to return it. Otherwise, the COUNTIFS formula would return 0. So the problem comes from the VLOOKUP.

Could you provide us some screenshots of the VLOOKUP with no sensitive datas on it for us to help?

David Joyeuse

Hi @Lisa Blair

Instead of using the IFERROR on the COUNTIFS function, use it on the VLOOKUP formula. Hence, instead of a #NO MATCH, you'll get a blank cell.

COUNTIF will deal better with BLANK cells than error cells I believe.

=IFERROR(VLOOKUP([Server Name]1, {cmdb_ci_win_server20200810 Range 1}, 3, false),"")

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

MCorbin

If any value in your [Server Install Status] column has "#No Match", you will get an error in your formula that is looking in that column.

The formula is ok, it's the data that's causing your problem. Go back to your data, find the row that has "#No Match" and see if you can resolve the lookup error.

David Joyeuse

Hi Lisa,

#NO MATCH in a COUNTIFS happens when you have a #NO MATCH on your VLOOKUP. And you only need one occurence of a #NO MATCH in the VLOOKUP for the COUNTIFS to return it. Otherwise, the COUNTIFS formula would return 0. So the problem comes from the VLOOKUP.

Could you provide us some screenshots of the VLOOKUP with no sensitive datas on it for us to help?

Lisa Blair

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

Lisa Blair

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.

David Joyeuse

Hi @Lisa Blair

Instead of using the IFERROR on the COUNTIFS function, use it on the VLOOKUP formula. Hence, instead of a #NO MATCH, you'll get a blank cell.

COUNTIF will deal better with BLANK cells than error cells I believe.

=IFERROR(VLOOKUP([Server Name]1, {cmdb_ci_win_server20200810 Range 1}, 3, false),"")

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

Lisa Blair

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! 😎