matching numbers

  • Thread starter Thread starter RodJ
  • Start date Start date
R

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
 
=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
 
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
 
"u have helped me for the second time today"

I did?

Anyway, thanks for the feedback

--


Regards,


Peo Sjoblom
 
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.
 
Back
Top