counting non matches in two (2) ranges

Hi All-

I'm looking to do this statement

=countif(a:a,"NOT IN" b2:b10)

So how do I do this?

Try

=COUNTA(A:A)-SUMPRODUCT(--ISNUMBER(MATCH(A:A,B2:B10,0)))

however, it is more efficient to use a defined range instead of A:A when using Sumproduct()..

Better yet, would be to add a helper column to return TRUE/FALSE matches at each row of column A and then use Countif(C:C,FALSE) where Column C contains TRUE/FALSE result of formula: =ISNUMBER(Match(A1,$B$2:$B$10,0)) copied down....

Hi,

The COUNTIF() function only has two elements. If you're trying to sum the amounts in B2:B10 rather than count them you should be using a SUMIF()

HTH

Yes... but slow