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

[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][td="bgcolor: #DCE6F1"]

H​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

1​

[/td][td]

3​

[/td][td]

3​

[/td][td]

4​

[/td][td]

2​

[/td][td]

8​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

1​

[/td][td]

1​

[/td][td]

2​

[/td][td]

3​

[/td][td]

4​

[/td][td]

5​

[/td][td]

5​

[/td][td]

7​

[/td][/tr]
[/table]

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. [TABLE="width: 175"]
<tbody>[TR]
[TD="class: xl63, width: 25"] 1
[/TD]
[TD="class: xl63, width: 25"] 1
[/TD]
[TD="class: xl63, width: 25"] 1
[/TD]
[TD="class: xl63, width: 25"] 3
[/TD]
[TD="class: xl63, width: 25"] 3
[/TD]
[TD="class: xl63, width: 25"] 4
[/TD]
[TD="class: xl63, width: 25"] 2
[/TD]
[/TR]
</tbody>[/TABLE]