Help with sum and countif

[Samgraphics]

Hi, I'm trying to create conditional formatting that will highlight the cell next to the number combination if that combination has 3 or more matching numbers and the winning number. I'm using =sum(countif()) but it's not producing the correct results. I'm not sure what is causing this. Can you please help me? Here is a snippet of the worksheet.
As you can see I tried different approaches but not getting desired results.

HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	LOTTERY TICKET PURCHASE HISTORY
2
3	LATEST DRAW
4	NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2
5	3	5	10	33	34	7	10			FALSE	< I was testing the formula here before going to conditional formatting
6
7
8	DATE	NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2	WINNERS
9	11/22/21	1	5	6	7	9	9	10
10		1	5	7	8	9	2	12
11		1	9	12	33	34	9	12
12		2	5	6	8	9	9	11
13		19	26	30	32	33	6	9
14		8	10	15	20	33	7	12
15		4	11	15	17	18	3	8
16		7	14	18	20	21	6	11
17		10	17	21	23	24	9	12
18		13	20	24	26	27	3	12
19		16	23	27	29	30	3	6
20		2	16	17	18	34	4	10
21		5	10	21	25	31	5	11
LotteryTable

Cell Formulas
Range	Formula
J5	J5	=SUM(COUNTIF($A9:$H9,$A$5:$G$5))>=3

Named Ranges
Name	Refers To	Cells
number07	=tbllottery[BONUS 2]	J5
number08	=tbllottery[BONUS 1]	J5
number09	=tbllottery[NO 5]	J5
numberCheck	=LotteryTable!$B5:INDEX(number09,COUNT(number09),1)	J5
numberCheck02	=LotteryTable!$G5:INDEX(number08,COUNT(number08),1)	J5
numberCheck03	=LotteryTable!$H5:INDEX(number07,COUNT(number07),1)	J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
I9:I88	Expression	=SUM(COUNTIF($A9:$H9,$A$5:$G$5))>=3	text	NO
A9:A88	Expression	=SUM(COUNTIF(B9:F9,A5:E5)+COUNTIF($G9:$H9,$F$5:$G$5))>=3	text	NO
H9:H88	Expression	=AND(COUNTIF($G$5,H9),COUNTIF(numberCheck03,H9)=1)	text	NO
H9:H88	Expression	=COUNTIF($G$5,H9)	text	NO
G9:G88	Expression	=AND(COUNTIF($F$5,G9),COUNTIF(numberCheck02,G9)=1)	text	NO
G9:G88	Expression	=COUNTIF($F$5,G9)	text	NO
B9:F88	Expression	=AND(COUNTIF($A$5:$E$5,B9),COUNTIF(numberCheck,B9)=1)	text	NO
B9:F88	Expression	=COUNTIF($A$5:$E$5,B9)	text	NO

Thank you in advance for your help and time.

ps I was finally able to install the xl2bb add in so if there are any problems i'm sorry.

 

[jasonb75]

You need to array confirm the formula for it to work. I believe that on a Mac it is done by pressing Cmd and Return / Enter together.
Note that conditional formatting formulas are entered as array by default so it will work fine there.

 

[RobVos]

I think the following will give you what you want:

=sumproduct(--($A$5:$G$5=B9:H9))>3

 

[etaf]

we are matching it row in A5 to G5 , which is the bonus balls
against the full like
B9 to H9
??
=SUMPRODUCT(COUNTIF(B9:H9,$A$5:$G$5))>2

see K & L for the formula working -
it can then be added to a condotional formatting

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	LOTTERY TICKET PURCHASE HISTORY
2
3	LATEST DRAW
4	NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2
5	3	5	10	33	34	7	10			TRUE	< I was testing the formula here before going to conditional formatting
6
7
8	DATE	NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2	WINNERS
9	44522	1	5	6	33	34	9	10			TRUE	2
10		1	5	7	8	9	2	12			FALSE	2
11		1	9	12	33	34	9	12			FALSE	1
12		2	5	6	8	9	9	11			FALSE	1
13		19	26	30	32	33	6	9			FALSE	4
14		8	10	15	20	33	7	12			TRUE	1
15		4	11	15	17	18	3	8			FALSE	1
16		7	14	18	20	21	6	11			FALSE	2
17		10	17	21	23	24	9	12			FALSE	1
18		13	20	24	26	27	3	12			FALSE	1
19		16	23	27	29	30	3	6			FALSE	3
20		2	16	17	18	34	4	10			TRUE	4
21		5	10	21	25	31	5	11			TRUE	0
Sheet2

Cell Formulas
Range	Formula
J5	J5	=SUM(COUNTIF($A9:$H9,$A$5:$G$5))>=3
K9:K21	K9	=SUMPRODUCT(COUNTIF(B9:H9,$A$5:$G$5))>2
L9:L21	L9	=SUMPRODUCT(COUNTIF(B10:H10,$A$5:$G$5))

 

[Samgraphics]

I think the following will give you what you want:

=sumproduct(--($A$5:$G$5=B9:H9))>3

Hi Jason and Rob, thank you for the quick response, much appreciated.
However, neither of these solutions are returning the correct results. I did the array but the results are not always right. There are times when there are only two matching numbers but it says true and times when there are more than 3 and it says false. I can't see what I'm missing. Did it return the correct results on your computer?
Here is a snippet.

HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	LOTTERY TICKET PURCHASE HISTORY
2
3		LATEST DRAW
4		NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2
5		2	4	9	33	34	7	10		FALSE	THIS ROW HAS MORE THAN 3 MATCHING NUMBERS IT SHOULD SAY TRUE
6										FALSE	this is true but doesn't update when the numbers are added or removed
7										FALSE	this should be true row 11 has 3 matching numbers
8	DATE	NO 1	NO 2	NO 3	NO 4	NO 5	BONUS 1	BONUS 2	WINNERS	TRUE	this is correct, but if I add or remove a matching number it doesn't update and it stays true all the time
9	11/22/21	1	5	6	7	9	9	10		TRUE	this was also correct but I changed a number in the latest draw to 5 an it didn't update it still says true when it should be false
10		1	5	7	8	9	7	12
11		1	9	12	33	34	9	12
12		2	5	6	8	9	9	11
13		19	26	30	32	33	6	9
LotteryTable

Cell Formulas
Range	Formula
J5:J7	J5	=SUMPRODUCT(--($B$5:$H$5=$B9:$H9))>=3
J8:J9	J8	=SUM(COUNTIF($B11:$H11,$B$5:$H$5))>=3
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
number07	=tbllottery[BONUS 2]	J5
number08	=tbllottery[BONUS 1]	J5
number09	=tbllottery[NO 5]	J5
numberCheck	=LotteryTable!$B5:INDEX(number09,COUNT(number09),1)	J5
numberCheck02	=LotteryTable!$G5:INDEX(number08,COUNT(number08),1)	J5
numberCheck03	=LotteryTable!$H5:INDEX(number07,COUNT(number07),1)	J5

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A9:A88	Expression	="{=SUM(COUNTIF(B9:F9,A5:E5)+COUNTIF($G9:$H9,$F$5:$G$5))>=3}"	text	NO
B9:F88	Expression	=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)	text	NO
B9:F88	Expression	=COUNTIF($B$5:$F$5,B9)	text	NO
G9:G88	Expression	=AND(COUNTIF($G$5,G9),COUNTIF(numberCheck02,G9)=1)	text	NO
G9:G88	Expression	=COUNTIF($G$5,G9)	text	NO
H9:H88	Expression	=AND(COUNTIF($H$5,H9),COUNTIF(numberCheck03,H9)=1)	text	NO
H9:H88	Expression	=COUNTIF($H$5,H9)	text	NO
I9:I88	Expression	=SUMPRODUCT(--($B$5:$H$5=B9:H9))>3	text	NO

 

[jasonb75]

Should the bonus numbers be matched to the other numbers or be counted separately?

As suggested the array confirmation of your formula does work correctly if the bonus numbers are to be cross matched. One thing that I did not notice was that you had included the date column in the first range which will be giving you some erroneous results.

The SUMPRODUCT formula suggested by @RobVos is probably not the way to go, it will only count the numbers that are in exactly the same position. in both tables (number 3 in the first column in both tables, 5 in the second column and so on).

 

[RobVos]

I am not clear if you need all matches or unique matches.

I didn't notice you put >=, so my first formula had only > -- change the 3 to 2 and see if that is what you wanted.

=SUMPRODUCT(--($A$5:$G$5=B9:H9))>2

Else maybe:

=SUMPRODUCT(COUNTIF(B9:H9,$A$5:$G$5))>2

 

[Samgraphics]

Should the bonus numbers be matched to the other numbers or be counted separately?

As suggested the array confirmation of your formula does work correctly if the bonus numbers are to be cross matched. One thing that I did not notice was that you had included the date column in the first range which will be giving you some erroneous results.

I would like the bonus numbers to be checked against bonus numbers only and the first 5 checked against the first five in each combination. I don't want the bonus numbers to be checked against the other numbers, no.
I also noticed that I accidentally included the date column, but I corrected it and still getting the wrong results.

 

[Samgraphics]

I am not clear if you need all matches or unique matches.

Maybe:

=SUMPRODUCT(COUNTIF(B9:H9,$A$5:$G$5))>3

Unique matches, please. No 1, no 2, no 3, no 4, no 5 with no1, no2,no3,no4,no5 and bonus1, with bonus1 and bonus2 with bonus2. I don't know if you can understand me. sorry. I don't want the bonus numbers to be checked against the other 5 numbers.

Thank you so much. sorry if i'm not being clear enough in what I need help with.

 

[etaf]

I would like the bonus numbers to be checked against bonus numbers only and the first 5 checked against the first five in each combination. I don't want the bonus numbers to be checked against the other numbers, no.

what does that now mean to the matching 3 ?

so you are matching

A5 to E5
agains B9 to F9
but is that for 3 mathes
and then how do the bonus balls get included ?

you can just change the range and add together

=SUMPRODUCT(COUNTIF(B9:F9,$A$5:$E$5))>2

=SUMPRODUCT(COUNTIF(B9:F9,$A$5:$E$5)) + SUMPRODUCT(COUNTIF(G9:H9,$F$5:$G$5))

or in a logic tst