The formula to count size of common set of two columns in excel

Suppose we have a row contains some value, such as A, B, C, D, E, and a row contains some value, such as F, G, A, B, E, I. Two columns have common set: A, B, E. In Excel we can count size of common set by using a formula that combines the COUNTIF and SUMPRODUCT functions.

The general formula: =SUMPRODUCT(COUNTIF(range1,range2))

range1‘ and ‘range2‘ are range of cells

Example:

A B
1 A E
2 B I
3 C A
4 D B
5 E
6 F
7 G

Formula: =SUMPRODUCT(COUNTIF(A1:A7,B1:B4))

The result is: 3