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:
1
2
3
4
5
and B1:B3 is:
3
4
8
the answer should be:
2
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