COUNTIF with text fields

[JimNC]

I have two list, A & B. B is a fixed list with the acceptable entries for
list A. List A items must be found in list B and there can be no duplicates
in list A.

Both lists are format as text and include entries such as "7", "07" which
should be recognized as not equal. COUNTIF returns a count of "2" for these
values.

Desired
Invalid entries in A return "0"
One valid entry returns a "1"
Two equal entries in List A return "2"
Three equal entries in List A return "3" etc.


Jim /*

WebApp Hosting
http://www.advanceddesignsinc.com/Web Hosting.htm

 

[Bob Phillips]

Jim,

Try this instead of COUNTIF

=SUMPRODUCT(--($A$1:$A$100=A1),--(LEN($A$1:$A$100)=LEN(A1)))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[JimNC]

It works for valid entries, but not invalid entries. I found a solution by
using Countif in one column and lookup in adjacent column.

Jim /*