How do I count rows that contain cells with only the first few letters matching

Question

I need to count the number of matches between cells in two columns. The data in the cell are names. Unfortunately, some of the names contain an asterisk at the end, so they are not an exact match. Is there a way to count the number of rows that contain matches between the two cells of only the first 3 letters or so?

Answer 1

Wildcards are tricky in some Excel formulas (and don't even look for using regular expressions!). For a simple count, however, using the COUNTIF function will work just fine. So if your data looks like this:

+----+-----+
|    |  A  |
+----+-----+
|  1 | AAA |
|  2 | AAB |
|  3 | AAC |
|  4 | ABA |
|  5 | ABB |
|  6 | ABC |
|  7 | ACA |
|  8 | ACB |
|  9 | ACC |
| 10 | ADA |
+----+-----+

Then you can count the cells in the range starting with "AA" by using the formula

=COUNTIF(A1:A10,"AA*")

The wildcard indicator * is what matches multiple cells and counts them.

Answer 2

as an alternative, you could use sumproduct to do you counting. Since sumproduct performs array like calculations you want to avoid using full column references and restrict it to your actual range of data. In cell D2 I used the following formula and copied down:

=SUMPRODUCT(--(LEFT($A$2:$A$8,LEN(C2))=C2))

This formula basically looks at the length you want to match and pulls only that many character from the left of the data you want to look through. It then compares that list with what you are looking for and generates a list of TRUE/FALSE. To convert TRUE/FALSE to 1 or 0, it needs to go through a math operation that does not change its value. In this case -- is being used, but +0, *1 could just as easily been used.

Answer 3

Similar to an earlier answer using SUMPRODUCT array but count comparing first three characters between cells per row ie A1 to B1, and A2 to B2 etc.

=SUMPRODUCT(--(LEFT(A1:A5,3)=LEFT(B1:B5,3)))

another possible solution to your asterisk issue can be taken into account when matching the whole word but substituting out the asterisk. (asterisk is character 42).

=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")))

This method does count empty cells as valid matches unless you add a further array to the SUMPRODUCT to count only if that the cell is not empty (less than or grater than empty).

=SUMPRODUCT(--(SUBSTITUTE(A1:A5,CHAR(42),"")=SUBSTITUTE(B1:B5,CHAR(42),"")),--(A1:A5<>""))

this with a small tweek would then allow whole column calculations

=SUMPRODUCT(--(SUBSTITUTE(A:A,CHAR(42),"")=SUBSTITUTE(B:B,CHAR(42),"")),--(A:A<>""))