Home > Net >  Formula to count cells in a range that match any value in cells in another range
Formula to count cells in a range that match any value in cells in another range

Time:03-21

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: FORMULA_SOLUTION

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

enter image description here

  • Related