I'd like to compare two columns and return total number of matches in Google Sheets.

COLUMN A
CAR
GIFT
RUN
DOG

COLUMN B
GIFT
GIFT
DOG
CAT

CAT
HUMAN
RUN
RUN
RUN

Comparing these two columns (where Column A is the dictionary) should return 6 (2 gifts, 1 dog, and 3 runs). It should be mentioned that blank cells should be ignored and should not count between the two columns. It should also be mentioned that column A is subject to change and more entries could be added at anytime (so I'd like to use A2:A range).

CodePudding user response：

You can try using a count and match

=arrayformula(count(match(B2:B, A2:A, 0)))

If in different sheet, just prepend the sheet name to the range

=arrayformula(count(match(Sheet2!A2:A, Sheet1!A2:A, 0)))

CodePudding user response：

Use this formula as it counts how many time each appears:

=ARRAYFORMULA(SUM(COUNTIF(A2:A,Sheet1!A2:A)))