is this a match ?

[Orangeanorak]

Can you please give me the formula to display the following

I have ten numbers in A1-A10

I have ten numbers in B1-B10 etc

in B11 I want to display the total times the numbers in b1:b10 match the numbers in a1:a10

in row A1-A10 3 5 8 11 22 34 37 45 50 53
row B1-B10 4 8 12 22 35 39 50 55 60 75

B11 would show 3

thank you very much for this

 

[hurgadion]

Hi,
try using an array formula (You should confirm it Ctrl+Shift+Enter):

=SUM(COUNTIF(A1:A10,B1:B10))

Best regards.

 

[hurgadion]

I think that I find another, array solution:

=20-SUM(1/COUNTIF(A1:B10,A1:B10))

Best regards.

 

[mikerickson]

If you want a non-CSE formula
=SUMPRODUCT(COUNTIF(A1:A10,B1:B10))

But, if you duplicates in either column, it might not do what you want.

 

[hurgadion]

For duplicates an array formula

=SUM(SIGN(COUNTIF(A1:A10,B1:B10)))

should be more suitable... Best regards.

 

[T. Valko]

Can you please give me the formula to display the following

I have ten numbers in A1-A10

I have ten numbers in B1-B10 etc

in B11 I want to display the total times the numbers in b1:b10 match the numbers in a1:a10

in row A1-A10 3 5 8 11 22 34 37 45 50 53
row B1-B10 4 8 12 22 35 39 50 55 60 75

B11 would show 3

thank you very much for this

Here's another one...

=SUMPRODUCT(--ISNUMBER(MATCH(B1:B10,A1:A10,0)))

 

[Aladin Akyurek]

For duplicates an array formula

=SUM(SIGN(COUNTIF(A1:A10,B1:B10)))

should be more suitable... Best regards.

The OP wants a count of the items from B1:B10 which match A1:A10.
That is, target range = B1:B10, criteria range = A1:A10.

[TABLE="width: 96"]
<tbody>[TR]
[TD="class: xl66, width: 64, bgcolor: #EBF1DE, align: right"]8
[/TD]
[TD="class: xl65, width: 64, bgcolor: transparent, align: right"]4
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]5
[/TD]
[TD="class: xl67, bgcolor: #EBF1DE, align: right"]8
[/TD]
[/TR]
[TR]
[TD="class: xl67, bgcolor: #EBF1DE, align: right"]8
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]12
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]11
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]22
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]22
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]35
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]34
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]39
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]37
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]50
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]45
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]55
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]50
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]60
[/TD]
[/TR]
[TR]
[TD="class: xl65, bgcolor: transparent, align: right"]53
[/TD]
[TD="class: xl65, bgcolor: transparent, align: right"]75
[/TD]
[/TR]
</tbody>[/TABLE]

The formula...

[1]

{=SUM(SIGN(COUNTIF(B1:B10,A1:A10)))}

would be affected by the duplicates that are in the criteria range while occurring just once in the target range...

Here it yields 4.

So would...

[2]

{=SUM(COUNTIF(B1:B10,A1:A10))}

[3]

=SUMPRODUCT(COUNTIF(B1:B10,A1:A10))

But...

[4]

=SUMPRODUCT(ISNUMBER(MATCH(B1:B10,A1:A10,0))+0)

returns a count of 3.

If the intent is to return the count of unique matching items, we would need something like:

[5]

{=SUM(IF(FREQUENCY(IF(ISNUMBER(MATCH(B1:B10,A1:A10,0)),
         MATCH(B1:B10,A1:A10,0)),ROW(B1:B10)-ROW(B1)+1),1))}

Note. Formulas with { and } around are array (CSE) formulas.

 

[Orangeanorak]

There will not be any duplicates

 

[Aladin Akyurek]

There will not be any duplicates

In that case, the formulas, numbered [1] to [5]. all will do, number [4] being a tad faster than others.

The one in [5] meets your expectation ('no duplicates') and avoids counting "unexpected duplicates."

 

[T. Valko]

number [4] being a tad faster than others.

You mean the one in post #6?