Counting the number of matches including repeats

[BrSuthe]

I need a formula to count the number of matches between two ranges even some numbers repeat itself. For example, if in row1 ( 1,1,1,3,3,4,2,8) and row2(1,2,3,4,5,5,7,7). Here there are 4 unique numbers match (1,2,3,4). But with repeating numbers, it is actually 7 numbers (1,1,1,3,3,2,4). I need a formula to count the latter method. I'd be glad if someone can suggest me a formula for this. Thanks!​

 

[JoeMo]

maybe:

=SUMPRODUCT((A1:H1=A2)+(A1:H1=B2)+(A1:H1=C2)+(A1:H1=D2)+(A1:H1=E2)+(A1:H1=F2)+(A1:H1=G2)+(A1:H1=H2))

 

[Aladin Akyurek]

Not sure, but does this also answer the query?

=SUMPRODUCT(--(COUNTIFS(A2:H2,A3:G3)>0))

 

[Marcelo Branco]

Maybe...

=SUMPRODUCT(COUNTIF(A1:H1,A2:H2))

M.

 

[BrSuthe]

Thanks, everybody for your help. I tried them, but didn't get the result I wanted. I found a solution though. Here it is:

=sumproduct(--isnumber(match(a1:h1,a2:h2,)))

Thanks again!

 

[Marcelo Branco]

Cross posted
https://www.excelforum.com/excel-fo...-the-number-of-matches-including-repeats.html

From Forum Rules
Rule #13:
We prefer that members do not cross-post questions to other forums, but when this does occur members should do the following:
- Post the details of your question on our forum. Do NOT simply post a link/re-direct to the question in another forum with no details posted here.
- Make it clear that you have cross-posted and provide links to the cross-posts.
Cross-posted questions that do not comply may be deleted or locked. For a discussion on the issues with cross-posting, see this link: Excelguru Help Site - A message to forum cross posters


M.

 

[Marcelo Branco]

Thanks, everybody for your help. I tried them, but didn't get the result I wanted. I found a solution though. Here it is:

=sumproduct(--isnumber(match(a1:h1,a2:h2,)))

Thanks again!

hmm... not sure this formula does what you want

Question: what would be the expected result for a scenario like this

	A​	B​	C​	D​	E​	F​	G​	H​
1​	1​	1​	1​	3​	3​	4​	2​	8​
2​	1​	1​	2​	3​	4​	5​	5​	7​

M.

 

[BrSuthe]

I am sorry about the cross-posting. I needed to get a quick reply so I did post twice.
and you are right about your example. In this case, it should be 6. but the formula gives 7. so it is not what i want. This means I still need a formula. It is strange though, I tested the formula first, and it worked. why it doesn't work in this scenario I don't understand.

 

[BrSuthe]

I correct my previous post. I made a mistake in counting. The formula gives 7, and it is correct. These numbers repeat.

1

1

1

3

3

4

2

<tbody>
</tbody>