Does anyone have any suggestion on how to count the matched values?

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I would like to count the number of values in column B, which is matched any
value in column A. In this case, the matched values are 6 & 9, and it will
return 2 in cell C1.
Does anyone have any suggestions?
Thank you for any suggestions
Eric

If you're not getting answers that work, you may want to rephrase your question
instead of just posting it again and again and again.

Eric wrote:

Does anyone have any suggestion on how to count the matched values?

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I would like to count the number of values in column B, which is matched any
value in column A. In this case, the matched values are 6 & 9, and it will
return 2 in cell C1.
Does anyone have any suggestions?
Thank you for any suggestions
Eric

--

Dave Peterson

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B3,A1:A8,0))))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestion on how to count the matched values?

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I would like to count the number of values in column B, which is matched
any
value in column A. In this case, the matched values are 6 & 9, and it will
return 2 in cell C1.
Does anyone have any suggestions?
Thank you for any suggestions
Eric

Thank everyone for suggestions

I would like to add a given condition for this matching.

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I still want to count the number of matched values in column B.
The condition is expanded the range for each value on column B and the range
value is stored in cell D1, which is 1.
For example, without applied the given condition, the value 4 in column B is
not a matched value on column A, because there is no 4 value in column A, but
once the condition applied, the value of 4 in column B will become any value
between 3 [4-1] and 5 [4+1].
If the range value is 2, then the value of 4 in column B will become any
value between 2 [4-2] and 6 [4+2].
Since the range value is 1 in this case and 5 in column A is matched the
range between 3 [4-1] and 5 [4+1], therefore the original value 4 in column B
becomes a matched value in this case.

Therefore the matched values with given condition are 6,4,9, which is 3
matched values and return 3 in cell C1.

Does anyone have any suggestions on how to add this given condition in order
to determine the number of matched values in column B?

Thank everyone very much for any suggesitons
Eric





"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(ISNUMBER(MATCH(B1:B3,A1:A8,0))))

--
Biff
Microsoft Excel MVP


"Eric" wrote in message
...
Does anyone have any suggestion on how to count the matched values?

There is a list of values under column A & B
[Column A] 1,5,6,8,9,10,3,20
[Column B] 6,4,9

I would like to count the number of values in column B, which is matched
any
value in column A. In this case, the matched values are 6 & 9, and it will
return 2 in cell C1.
Does anyone have any suggestions?
Thank you for any suggestions
Eric