0

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?

2
  • 1
    Using COUNTIF should do the trick. For example, looking in a range in column M for entries beginning with "AA" would be =COUNTIF(M1:M1000,"AA*") – PeterT Oct 3 '18 at 17:59
  • 1
    @PeterT Sounds like a good answer to me, why don't you post it? – cybernetic.nomad Oct 3 '18 at 18:26
0

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.

0

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.

POC

0

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)))

sum product left

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),"")))

enter image description here

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<>""))

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.