count #N/A values

[MetLife]

Hi,

I have a column of vlookups and some of them are returning #N/A values. Is there anyway to count these using the 'countifs()' function?

I wanted to avoid adding a second column "iserror()" and counting that.

Thanks,

 

[Joe4]

You can use an XLOOKUP function instead of VLOOKUP, in which you can tell Excel what to return if there is no match (instead of returning #N/A).
Then, you can just use a COUNTIF function to count the number of that value you told it to return.
See: XLOOKUP function - Microsoft Support

Example 3 in that link shows exactly how to do that (return something other than #N/A for no match).

 

[Scott Huish]

To directly answer your question, you can count #N/A

=COUNTIF(A1:A10,"#N/A")

 

[Joe4]

To directly answer your question, you can count #N/A

=COUNTIF(A1:A10,"#N/A")

It always baffles me why that works with some functions any not others.

For example, if you wanted to check if a cell specifically equals that value, and tried something like this (where the "#N/A" was in cell H3):

=IF(H3="#N/A","No Match","Match")

It will never return "No Match". If H3 is #N/A, that formula will return #N/A.

 

[Scott Huish]

I think the reason is is that COUNTIF counts what is returned to the cell, either number or text. It's showing the text #N/A, but the underlying value is the error #N/A
So asking if H3="#N/A" would not be equal because the actual value in cell H3 is the error #N/A, not a text string.

You can count #DIV/0! too, but can't check for equal to that directly either.

 

[Joe4]

Thanks for the explanation!