Excel Formula To Count Matches

January 6, 2011 at 14:17:36
Specs: Windows XP
I have two rows. Each row is containing numbers.

The first row containers numbers I play with the lotto.

In the second row I enter the winning numbers.

In a cell I want a formula that makes a count of the matching numbers between the first and the secon row.

Thank you in advance

edited by moderator: subject line edited to be relevant


See More: Excel Formula To Count Matches

Report •


#1
January 6, 2011 at 15:38:31
I'm assuming that there are never any duplicates in the numbers you choose or the winning numbers.

For a set of 5 numbers try:

=SUMPRODUCT(--(ISNUMBER(MATCH(A1:E1, A2:E2, 0))))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 7, 2011 at 07:35:22
Hey,

Thank you so much. I was using the following formula:

=SUM(COUNTIF($B$1:$G$1,$B3),COUNTIF($B$1:$G$1,$C3),COUNTIF($B$1:$G$1,$D3),COUNTIF($B$1:$G$1,$E3),COUNTIF($B$1:$G$1,$F3),COUNTIF($B$1:$G$1,$G3)

Of course this is to long and unpractical.

Once again thank you so much. I am also so supprised I received your reply so fast.

Have a great day,

Phil


Report •
Related Solutions


Ask Question