ExcelBanter - Counting ISNAs?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Counting ISNAs? (https://www.excelbanter.com/excel-discussion-misc-queries/238559-counting-isnas.html)

I want to check how many of the entries in A1:A10, are not found to be
existing in another list, suppose B1:B20.

Tried out:

=SUMPRODUCT(--(ISNA(VLOOKUP(A1:A10,B1:B20,1,0))))

but failed. What might be the reason? and what might be its solution except
then a helper column?

--
Best Regards,

Faraz

Try the array formula

=SUM(IF(A1:A10<"",IF(ISNA(MATCH(A1:A10,B1:B20,0)) ,1,0)))

If this post helps click Yes
---------------
Jacob Skaria

"Faraz A. Qureshi" wrote:

I want to check how many of the entries in A1:A10, are not found to be
existing in another list, suppose B1:B20.

Tried out:

=SUMPRODUCT(--(ISNA(VLOOKUP(A1:A10,B1:B20,1,0))))

but failed. What might be the reason? and what might be its solution except
then a helper column?

--
Best Regards,

Faraz

Thanx Jacob!

Nice 2 find u after such a longtime! Was on an official leave actually. Sure
would oblige if u would also answer my question on the programming panel as
"Counting Errors".

Thanx in advance.
--
Best Regards,

Faraz

"Jacob Skaria" wrote:

Try the array formula

=SUM(IF(A1:A10<"",IF(ISNA(MATCH(A1:A10,B1:B20,0)) ,1,0)))

If this post helps click Yes
---------------
Jacob Skaria

"Faraz A. Qureshi" wrote:

I want to check how many of the entries in A1:A10, are not found to be
existing in another list, suppose B1:B20.

Tried out:

=SUMPRODUCT(--(ISNA(VLOOKUP(A1:A10,B1:B20,1,0))))

but failed. What might be the reason? and what might be its solution except
then a helper column?

--
Best Regards,

Faraz