countif partial match

  • Thread starter Thread starter cpliu
  • Start date Start date
C

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,
 
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,
 
Back
Top