Formula to count cells in a range that match any value in cells in another range-CodePudding

I am looking for a formula that will count the number of cells in a range (say A1:A5) whose values match any of the values of another range (say B1:B3).

If A1:A5 is:

and B1:B3 is:

3
4
8

the answer should be:

since A3 and A4 match something in B1:B3.

I expected the method shown here to work:

CodePudding user response：

Count Matches

As Solar Mike mentioned in the comments: Perhaps count matches.

=COUNT(MATCH($A$1:$A$10,$B$1:$B$10,0))
=SUMPRODUCT(--ISNUMBER(MATCH($A$1:$A$10,$B$1:$B$10,0)))
=SUMPRODUCT(--(COUNTIF($B$1:$B$10,$A$1:$A$10)>0))

The COUNTIF Trap