Collecting lottery data and need to count how many times a pair of numbers shows up

[TheseStars]

Greetings!

New forum member here and a novice at best at Excel. Could use some help, not able to solve my problem on my own.

---I'm struggling at the moment trying to find the correct formula that will count how many times a pair of numbers are showing up together.

I'm using MS Office 2016 Excel and tracking my states pick 3 lottery results.

I want to count how many times the 2 is showing up with the 0 across a 30 or 31 day period. I want to do this for all 45 pairs.

Columns x, y and z are holding the draw results for the 1st, 2nd, and 3rd position of the results, so my range is of course 30 or 31 sets (depending on month) of 3 digit numbers each in their own cell.

any help would be appreciated, I tried everything I think I knew, but not having any luck.

Thanks!

 

[scm160]

Greetings!

New forum member here and a novice at best at Excel. Could use some help, not able to solve my problem on my own.

---I'm struggling at the moment trying to find the correct formula that will count how many times a pair of numbers are showing up together.

I'm using MS Office 2016 Excel and tracking my states pick 3 lottery results.

I want to count how many times the 2 is showing up with the 0 across a 30 or 31 day period. I want to do this for all 45 pairs.

Columns x, y and z are holding the draw results for the 1st, 2nd, and 3rd position of the results, so my range is of course 30 or 31 sets (depending on month) of 3 digit numbers each in their own cell.

any help would be appreciated, I tried everything I think I knew, but not having any luck.

Thanks!

Please try this:

=COUNTIF(A1:A10,2)

Countif statement has two arguments (separated by commas) the Range = A1:A10 (area of data you want to evaluate) and Criteria "2" or some other logical statement.

An example of a logical statement >2 or "how many numbers appear in my range that are greater than 2?"

 

[TheseStars]

Thanks for your help, I'm familiar with that formula, as I use it to determine how many times a number shows up in the first position during the course of a month.

but what I need now is different, I now need to find out how many times a pair of numbers show up together ---such as 2 and 0 during the course of the month

 

[StephenCrump]

Try:

G2: {=SUM(--(MMULT(($A$3:$C$22=E2)+($A$3:$C$22=F2),{1;1;1})=2))} Array-entered, copy down

Total =60 as expected, equals 3 pairs per day x 20 days in this case

	A	B	C	D	E	F	G
1	LOTTERY				Your pairs		# times
2	No #1	No #2	No #3		0	1	3
3	4	7	5		0	2	1
4	8	6	5		0	3	0
5	5	4	3		0	4	3
6	9	0	8		0	5	1
7	1	3	2		0	6	2
8	2	1	0		0	7	1
9	8	4	1		0	8	3
10	8	9	0		0	9	4
11	5	9	8		1	2	3
12	9	0	4		1	3	1
13	1	0	6		1	4	1
14	9	1	7		1	5	0
15	0	7	1		1	6	1
16	7	2	1		1	7	3
17	4	8	2		1	8	1
18	5	6	0		1	9	1
19	8	5	2		2	3	2
20	8	0	4		2	4	1
21	0	4	9		2	5	1
22	2	8	3		2	6	0
23					2	7	1
24					2	8	3
25					2	9	0
26					3	4	1
27					3	5	1
28					3	6	0
29					3	7	0
30					3	8	1
31					3	9	0
32					4	5	2
33					4	6	0
34					4	7	1
35					4	8	3
36					4	9	2
37					5	6	2
38					5	7	1
39					5	8	3
40					5	9	1
41					6	7	0
42					6	8	1
43					6	9	0
44					7	8	0
45					7	9	1
46					8	9	3
47					Total		60

<tbody>
</tbody>

​

 

[TheseStars]

Yes! Wow! Perfect! this is exactly what I meant I needed. Big stack of thanks for taking the time to illustrate ... I knew I would need brackets and + sign, but the rest isn't making sense to me and I would of never figure that out on my own. That said,

I did entered it in exactly as you illustrated replacing with my own cell ranges and ending my formula with the last bracket. But it didn't work --- I'm wondering what you mean by "...array-enter..." was I suppose to but the range of where my number results are? I know what you mean by copy down. just not sure if I was suppose to add something else after the last bracket.

I am not getting an error message when I hit enter, the formula just shows up in the cell. What do you think I missed?

This is what I entered.
{=sum(--(MMULT(($X$6:$Y$36=K3)+($X$6:$Y$36=L3),{1;1;1})=2))}

 

[StephenCrump]

Array-enter means you hold down the CTRL and SHIFT keys before you hit ENTER

Put in the formula:

G2: =SUM(--(MMULT(($A$3:$C$22=E2)+($A$3:$C$22=F2),{1;1;1})=2)) with CTRL-SHIFT-ENTER

and Excel creates the curly brackets to show that it is an array-formula.

 

[TheseStars]

....and the magic begins!!!!
Thank you so much Steven, it worked like a charm.
Can't thank you enough.

 

[TheseStars]

quick question? why the =2 at the end? I would really like to understand the logic of the formula---
sorry to be such a pain.

 

[StephenCrump]

Thank you so much Steven, it worked like a charm.

You're welcome.

quick question? why the =2 at the end?

Consider a simple example:

G2: =SUM(--(MMULT(($A$3:$C$12=E2)+($A$3:$C$12=F2),{1;1;1})=2)) Array-entered

Now let's break this down using some helper cells:

Select the range I3:K2. In the formula bar, enter the array formula: =($A$3:$C$12=E2)+($A$3:$C$12=F2) ie CTRL-SHIFT-ENTER

Select the range M3:M12. In the formula bar, enter the array formula =MMULT(I3:K12,{1;1;1})

The cells highlighted are those we're interested in. The 2's we're looking for (6 occurrences) are where we have a match for the two numbers in the pair.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	LOTTERY				Your pairs		# times
2	No #1	No #2	No #3		0	8	6
3	0	9	8						1	0	1		2
4	1	9	8						0	0	1		1
5	0	9	8						1	0	1		2
6	1	9	8						0	0	1		1
7	9	8	0						0	1	1		2
8	1	9	8						0	0	1		1
9	0	9	8						1	0	1		2
10	1	9	8						0	0	1		1
11	9	8	0						0	1	1		2
12	8	9	0						1	0	1		2

<tbody>
</tbody>

​

 

[TheseStars]

Good Morning Steven,

I got back at it this morning, as I'm very excited to solve my problem and saw your above explanation. Thanks again for taking the time to show me the steps. I so terribly want to understand. I just love Excel and well, I've been my only teacher. Still not quite sure I get why the =2.

In addition, I'm

Sorry to come back here defeated, but I counted the count feedback using the formula given and its incorrect.

I put a clipped copy of my 2016 Ohio results sheet below. You'll notice I'm collecting results data for both the midday draw game and the evening draw game. (I use them against one another to deduce possibilities). Midday game and its game data is on the left side of Column Y and the evening game is to the right of column Y.

Looking to the left at midday game, In column K4, I entered this formula as you gave it me changing only the cell ranges. {=SUM(--(MMULT(($U$6:$W$36=I3)+($U$6:$W$36=J3),{1;1;1})=2))}

below are just 3 examples where the return from the formulas is wrong.

Incorrect: In column K (the above formula column) the return was a 0 times that 0 show with the 2, but that's incorrect because on January 19 the midday results was 280. So the formula needs to return a count of 1.

Correct: In column K row k5 the results for the pair 04 was 2, which is correct in that the pair did in fact show up together on January 14th and 15.

Incorrect: In column K row K6 the results for the pair 01 returned a zero count, yet on January 25th the results was 081 and the forumula should of counted 1 for that pair.

Here is my conclusion. I thought that perhaps it didn't count because the numbers were not in fact next to one another in the cell range. For my work, the pair 0 and 2 could show up in any position, they don't have to be adjacent to each other, but would still show up as a pair if the number drawn was 280. In Ohio, you can play pairs, front pair, back pair and split pair, the number 280 would be a spit pair if you thought the 2 with the 0 was due.

because I don't fully understand the formula you gave me, I'm wondering if it has to be adjusted? so that it does'nt matter if when the two numbers in the questioned pair fall in the results. I'm thinking the formula has to read so that it looks at each midday for the month separately in Columns U, V, W.

Completely understand if your tired of this problem. any thank you for all the time you've generously spent to help.

Cheers!