countif partial match

[cpliu]

How do you countif on partial match? To be specific, only compare the
last 3 digits and count the total match if the last 3 digits match.

For example:
=COUNTIF($a$2:$a$100,b1)

b1 = ABC123

if a2 to a100 has AAA123, it would still be considered as a match
(even though ABC is different from AAA) and get one for hte result.

Any way to do that?

Thanks for the help,

 

[Domenic]

Try...

=COUNTIF($A$2:$A$100,"*"&RIGHT(B1,3))

 

[RagDyer]

Try this:

=SUMPRODUCT(--(ISNUMBER(FIND(RIGHT(B1,3),RIGHT(A2:A100,3)))))

 

[liu]

Thank you for the help. It kind of works but my data may have
exceptions that both functions can't address.

A few questions:
1. Do the last 3 characters need to be numbers? I found some data I
have has a letter at the end, and result does not look right.
2. Right(B1,3) means extract the last 3 characters to B1, right?
3. What does -- in the second function mean?
4. What does "*" in the first function mean?

Any pointers.

I have a few entry like XYZ123A and XYZ123D. The result is one while
they should be 0 since 123A does not equal to 123D. How can I modify
the function to work on these situations (with extra letter at the
end, so it needs to compare the last 4 characters)?

Thanks,