To count the number of common values you can also use the simpler:
=SUM(--(ISNUMBER(MATCH($A$1:$A$12,$B$1:$B$10,0)))) (array-entered)
MATCH($A$1:$A$12,$B$1:$B$10,0) evaluates to:
{10,#N/A,#N/A,5,#N/A,1,2,#N/A,#N/A,#N/A,#N/A,2}
meaning that the first value in A1:A12 is in the tenth position in B1:B10. The values in the second and third value in A1:A12 are not in B1:B10 and so on.
ISNUMBER(MATCH($A$1:$A$12,$B$1:$B$10,0)) evaluates to:
{TRUE,FALSE,FALSE,TRUE,FALSE,TRUE,TRUE,FALSE,FALSE ,FALSE,FALSE,TRUE}
and by double negation (--) these Boolean values are converted from a lot of TRUE, FALSE's to:
{1,0,0,1,0,1,1,0,0,0,0,1}
which inserted in the SUM function returns 5.
|