Count values that do not appear in range

[Rhodes19]

This seems like it should be simple, but I'm stumped. How would I count the number of values from column B that do not appear in column A? Looking for a formula that returns the total count. The actual data set is much more complex than this, so I'm ideally hoping to avoid adding extra columns with individual cell lookups.

Thanks for your help!

 

[Fluff]

How about

=COUNT(FILTER(ROW(B2:B11),ISNA(MATCH(B2:B11,A2:A11,0))))

 

[Rhodes19]

Oh, this is my mistake--I should have noted that I can't use Excel 365 functions because this is a work project and my manager won't upgrade his Excel 2013. Believe me, it's not ideal...

 

[Rick Rothstein]

Here is another formula that should work...

=SUM(0+ISERROR(MATCH(B2:B9,A2:A9,0)))

 

[Rick Rothstein]

Oh, this is my mistake--I should have noted that I can't use Excel 365 functions because this is a work project and my manager won't upgrade his Excel 2013. Believe me, it's not ideal...

Give this formula a try (change the A and B column references as needed)...

=SUMPRODUCT(0+ISERROR(MATCH(B2:B9,A2:A9,0)))

 

[Rhodes19]

Perfect! Thank you both! Let's see if I can mark these both as solutions...

 

[Fluff]

Glad we could help & thanks for the feedback.