matching numbers

[RodJ]

Would appreciate help with this request:

I have two sets of numbers in rows: eg:

2,10,16,20,38,39,42,45
3,10,15,32,36,38,40,42

I want a function that would tell me how many in the first row match with
the second row? Answer I am looking for is = 3.

Thanks in advance
RodJ

 

[Peo Sjoblom]

=SUM(COUNTIF($A$2:$H$2,{2,10,16,20,38,39,42,45}))

or


=SUM(COUNTIF($A$2:$H$2,$A$1:$H$1))


entered with ctrl + shift & enter


or


=SUMPRODUCT(COUNTIF($A$2:$H$2,$A$1:$H$1))


entered normally




--


Regards,


Peo Sjoblom

 

[TomPl]

Assume columns A,B,C,D,E,F,G,H
Assume rows 1 and 2
Put the fomula =count($A$1:$H$1,A2) in cell A3
Copy that formula to cells B3:H3
Then sum cells A3:H3 to get the total number of matches for your lottery pick.

Tom

 

[RodJ]

Fantastic..it worked...u have helped me for the second time today..bless you...

 

[RodJ]

Thanks very much...good guess i am building a lotto template..thank u once
again...

 

[Peo Sjoblom]

"u have helped me for the second time today"

I did?

Anyway, thanks for the feedback

--


Regards,


Peo Sjoblom

 

[karan]

would need to check about 10 numbers. used the formula but need adding = sign
before checking 1 set of numbers against the 10 numbers, any ways to get
round them.