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%20Hosting.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" <[email protected]> wrote in message
news:[email protected]...
> 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%20Hosting.htm
>
>

[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 /*



"Bob Phillips" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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%20Hosting.htm
>>
>>
>
>