Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help comparing 6 of 10 numbers
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help comparing 6 of 10 numbers
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)+COUNT IF($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)+C OUNTIF($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)+COUNT IF($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)+C OUNTIF($A$1:$F$1,K3) The numbers don't even have to be in sequence on any of the rows. " wrote: 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help comparing 6 of 10 numbers
On Mar 6, 5:51 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis)
wrote: 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)+COUNT IF($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)+C OUNTIF($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)+COUNT IF($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)+C OUNTIF($A$1:$F$1,K3) The numbers don't even have to be in sequence on any of the rows. " wrote: 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. Thank you..That worked great. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help comparing 6 of 10 numbers
Hello,
Array enter (enter with CTRL + SHIFT + ENTER, not just ENTER): =SUM(--ISNUMBER(MATCH(A1:F1,A3:J3,))) Regards, Bernd |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Comparing 3 numbers | Excel Discussion (Misc queries) | |||
Comparing two columns of numbers | Excel Worksheet Functions | |||
comparing numbers in a sheet | Excel Discussion (Misc queries) | |||
Comparing numbers/figures | Excel Worksheet Functions | |||
comparing 2 columns of numbers | Excel Programming |