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!

 

[StephenCrump]

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))}

You'll get funny results if your formulae point to the previous row!

But there is a bigger problem. I see from your screenshot that the lottery results can be duplicated, e.g. a possible draw would be 9|9|9, which I haven't allowed for.

Here's a quick fix. The formula is not particularly elegant, but I can't immediately come up with anything more succinct.

G2: =SUMPRODUCT((MMULT(--($A$3:$C$10=E2),{1;1;1})>=COUNTIF(E2:F2,E2))*(MMULT(--($A$3:$C$10=F2),{1;1;1})>=COUNTIF(E2:F2,F2))) (No need to array-enter this time)

	A	B	C	D	E	F	G
1	LOTTERY				Your pairs		# times
2	No #1	No #2	No #3		1	1	4
3	1	1	1		1	9	6
4	1	1	9		9	9	4
5	1	9	1
6	1	9	9
7	9	1	1
8	9	1	9
9	9	9	1
10	9	9	9

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

 

[TheseStars]

Thanks Stephen,

I will go this new one a go in the am! Stay tuned.

Funny how the word elegant has a whole new meaning.

Cheers,

 

[TheseStars]

Hey Stephen,

Looks like I'm still having some trouble, here's a cut and paste of the formula with the row and column headings,

I started to do did a rough count of the pairs running down in Column G just so I could see if I was getting close to victory testing what you give me.

K3 shows what I entered. I can't figure out what I'm doing wrong.

Too many 0's are showing up when the return should be at least 1 or greater. (my rough count is not complete nor accurate).

 

[StephenCrump]

It works for me:

K3: =SUMPRODUCT((MMULT(--($U$6:$W$36=I3),{1;1;1})>=COUNTIF(I3:J3,I3))*(MMULT(--($U$6:$W$36=J3),{1;1;1})>=COUNTIF(I3:J3,J3)))

Do you have Calculation set to manual, or circular references?

	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
2	Pair		Count
3	0	2	1
4	0	4	2										Lottery
5	5	9	3
6	7	7	3										5	9	4
7													1	6	3
8													4	1	4
9													9	2	5
10													2	9	9
11													5	2	1
12													7	0	7
13													4	3	9
14													6	6	2
15													3	1	4
16													7	2	4
17													5	9	7
18													9	7	7
19													6	4	0
20													4	0	5
21													6	0	0
22													8	9	9
23													6	1	4
24													2	8	0
25													4	4	3
26													1	5	8
27													7	8	0
28													8	8	8
29													2	9	4
30													0	1	8
31													7	7	7
32													8	7	0
33													1	2	5
34													5	5	6
35													6	3	1
36													9	9	4

<tbody>
</tbody>

​

 

[TheseStars]

Thanks again, I'm going to try to move to another cell.

Not getting a circular reference message, or a error.

I just changed my my calculations set to Automatic.

Still didn't work. Going to try something. Will get back to you.

Thanks again. Really appreciate all your help.

 

[StephenCrump]

If you're still stuck, and happy to post your workbook (or a cut-down version) you can always upload to a provider like box.com and post the link here.

 

[TheseStars]

By all means, Stephen,

I'm still stuck, here is link to a folder I made on Dropbox.com.

I put a file in there called Mr. Excel---please, do you thing. As much as I would love to understand
I just can't seem to get this one figured out.

https://www.dropbox.com/sh/3cg1hapnfp8fgik/AABWWHmkaTjZAwJr-QAQ7OJ1a?dl=0

Thanks!!!! Big Time.

 

[StephenCrump]

Your numbers in I:J are formatted as text. Change to numeric and the formula appears to work OK.