Need help comparing 6 of 10 numbers

[bradchap]

Hi Excel guru's. I need help. I am doing a fundraiser lottery.
People are to pick 10 numbers between 1 and 49, I am then going to
choose 6 random numbers. I need a way of checking the numbers with
the 10 they have picked. I would like to then have it display how
many numbers matched. If you could help me I would greatly appreciate
it.

(e-mail address removed)

 

[JLatham]

Put the 6 numbers you pick in row 1, columns A-F

Start putting the chosen 10 numbers in rows starting at 3 (they go into A-J)
In K3 put this formula (it's all one formula)
=COUNTIF($A$1:$F$1,A3)+COUNTIF($A$1:$F$1,B3)+COUNTIF($A$1:$F$1,C3)+COUNTIF($A$1:$F$1,D3)+COUNTIF($A$1:$F$1,E3)+COUNTIF($A$1:$F$1,F3)+COUNTIF($A$1:$F$1,G3)+COUNTIF($A$1:$F$1,H3)+COUNTIF($A$1:$F$1,I3)+COUNTIF($A$1:$F$1,J3)

It will give you the count of the number of matches on the player's row with
the 6 numbers you have in row 1. You could put everyone's numbers in rows on
down the sheet and extend the formula down to check them all.

If you want to put person's name in column A (starting at row 3) and then
follow with the 10 numbers they pick, then those numbers would go into B-K,
and the counting formula would go into L and would look like this:

=COUNTIF($A$1:$F$1,B3)+COUNTIF($A$1:$F$1,C3)+COUNTIF($A$1:$F$1,D3)+COUNTIF($A$1:$F$1,E3)+COUNTIF($A$1:$F$1,F3)+COUNTIF($A$1:$F$1,G3)+COUNTIF($A$1:$F$1,H3)+COUNTIF($A$1:$F$1,I3)+COUNTIF($A$1:$F$1,J3)+COUNTIF($A$1:$F$1,K3)

The numbers don't even have to be in sequence on any of the rows.

 

[bradchap]

Put the 6 numbers you pick in row 1, columns A-F

Start putting the chosen 10 numbers in rows starting at 3 (they go into A-J)
In K3 put this formula (it's all one formula)
=COUNTIF($A$1:$F$1,A3)+COUNTIF($A$1:$F$1,B3)+COUNTIF($A$1:$F$1,C3)+COUNTIF($A$1:$F$1,D3)+COUNTIF($A$1:$F$1,E3)+COUNTIF($A$1:$F$1,F3)+COUNTIF($A$1:$F$1,G3)+COUNTIF($A$1:$F$1,H3)+COUNTIF($A$1:$F$1,I3)+COUNTIF($A$1:$F$1,J3)

It will give you the count of the number of matches on the player's row with
the 6 numbers you have in row 1. You could put everyone's numbers in rows on
down the sheet and extend the formula down to check them all.

If you want to put person's name in column A (starting at row 3) and then
follow with the 10 numbers they pick, then those numbers would go into B-K,
and the counting formula would go into L and would look like this:

=COUNTIF($A$1:$F$1,B3)+COUNTIF($A$1:$F$1,C3)+COUNTIF($A$1:$F$1,D3)+COUNTIF($A$1:$F$1,E3)+COUNTIF($A$1:$F$1,F3)+COUNTIF($A$1:$F$1,G3)+COUNTIF($A$1:$F$1,H3)+COUNTIF($A$1:$F$1,I3)+COUNTIF($A$1:$F$1,J3)+COUNTIF($A$1:$F$1,K3)

The numbers don't even have to be in sequence on any of the rows.

Thank you..That worked great.

 

[Bernd]

Hello,

Array enter (enter with CTRL + SHIFT + ENTER, not just ENTER):
=SUM(--ISNUMBER(MATCH(A1:F1,A3:J3,)))

Regards,
Bernd