Help with sum and countif

S

Samgraphics

Board Regular
Joined
Jan 9, 2022
Messages
61
Office Version
  1. 2011
Platform
  1. MacOS
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
ABCDEFGHIJKLMNOP
1LOTTERY TICKET PURCHASE HISTORY
2
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2
535103334710FALSE< I was testing the formula here before going to conditional formatting
6
7
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS
911/22/2115679910
1015789212
1119123334912
1225689911
13192630323369
14810152033712
1541115171838
16714182021611
171017212324912
181320242627312
19162327293036
20216171834410
21510212531511
LotteryTable
Cell Formulas
RangeFormula
J5J5=SUM(COUNTIF($A9:$H9,$A$5:$G$5))>=3
Named Ranges
NameRefers ToCells
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
CellConditionCell FormatStop If True
I9:I88Expression=SUM(COUNTIF($A9:$H9,$A$5:$G$5))>=3textNO
A9:A88Expression=SUM(COUNTIF(B9:F9,A5:E5)+COUNTIF($G9:$H9,$F$5:$G$5))>=3textNO
H9:H88Expression=AND(COUNTIF($G$5,H9),COUNTIF(numberCheck03,H9)=1)textNO
H9:H88Expression=COUNTIF($G$5,H9)textNO
G9:G88Expression=AND(COUNTIF($F$5,G9),COUNTIF(numberCheck02,G9)=1)textNO
G9:G88Expression=COUNTIF($F$5,G9)textNO
B9:F88Expression=AND(COUNTIF($A$5:$E$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($A$5:$E$5,B9)textNO


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.
 
Sort by date Sort by votes
etaf said:
what does that now men to the matching 3 ?
Click to expand...
HI, I'm so sorry I'm not being clear enough. Thank you for your time and help. here is a snippet with an explanation example. hope it helps clarify what I need. Thank you
HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
JKLMNOPQRST
12LATEST DRAW
13NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2
142493334710
15
16MY TICKET
17NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2
183593234610
19
20EXAMPLE
21I WOULD LIKE K14:O14 CHECKED AGAINST K18:O18 FOR MATCHES WHICH ARE 9 & 34 = 2
22THEN P14:Q14 CHECKED AGAINST P18:Q18 FOR MATCHES WHICH IS 10 = 1
23THEN ADD THE TWO TOTALS 2 + 1 = 3
24IF THE TOTAL MATCHES IN MY TICKET, WHICH ARE 9, 34 AND 10 ADD UP TO 3 OR MORE THEN HIGHLIGHT THE CELL
LotteryTable
 
Upvote 0

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
I think I know what you want now:

Excel Formula: 
=SUMPRODUCT(COUNTIF(B9:F9,$A$5:$E$5))+COUNTIF(G9,$F$5)+COUNTIF(H9,$G$5)
 
Upvote 0
RobVos said:
I think I know what you want now:

Excel Formula: 
=SUMPRODUCT(COUNTIF(B9:F9,$A$5:$E$5))+COUNTIF(G9,$F$5)+COUNTIF(H9,$G$5)
Click to expand...
YESSSSSSSSS! THANK YOU, THANK YOU, THANK YOU!
THANK YOU FOR YOUR TIME AND HELP.

I ALSO WANT TO SAY THANK YOU TO ALL THE OTHERS WHO TRIED TO HELP ME, ETAF AND JASONB75. I'M SORRY I WASN'T ABLE TO EXPLAIN MYSELF CLEARLY IN THE BEGINNING, CAUSING YOU TO WASTE YOUR TIME. I REALLY APPRECIATE YOUR HELP. THANK YOU ALL.
 
Upvote 0
RobVos said:
Glad we were able to help out.
Click to expand...
HI ROB, sorry to bother you again, also that it didn't occur to me earlier to do this step, but I was wondering if you could help me out again, please? I wanted to add some text to the formula to make it more intuitive I guess. I added an if statement so if it has 3 matches it would put the amount of money won, but there are different cash prizes for 3, 4, 5, etc matches. Is there a way to do that without making the formula too cluttered or too long? Can you please help me out? Thank you.

HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
ABCDEFGHIJ
1LOTTERY TICKET PURCHASE HISTORY
2
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2
52493234910¥ 5
6¥ 0
7¥ 5
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 5
911/22/2115679910¥ 0
1015789712
1119123334912
1225689911
13192630323369
LotteryTable
Cell Formulas
RangeFormula
J5:J9J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
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
CellConditionCell FormatStop If True
A9:A88Expression="{=SUM(COUNTIF(B9:F9,A5:E5)+COUNTIF($G9:$H9,$F$5:$G$5))>=3}"textNO
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:G88Expression=AND(COUNTIF($G$5,G9),COUNTIF(numberCheck02,G9)=1)textNO
G9:G88Expression=COUNTIF($G$5,G9)textNO
H9:H88Expression=AND(COUNTIF($H$5,H9),COUNTIF(numberCheck03,H9)=1)textNO
H9:H88Expression=COUNTIF($H$5,H9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO
 
Upvote 0
Samgraphics said:
for 3, 4, 5, etc matches. Is there a way to do that without making the formula too cluttered or too long?
Click to expand...
There are ways but we would need a clearer outline of the required results in order to set up the formula.

This will give you "¥ 5" for 3 matches, "¥ 10" for 4, "¥ 20" for 5, or "¥ 0" for anything else.
Excel Formula: 
=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
We will need the specifics of etc to include anything else. Adding 6 or 7 matches is easy but if there are different results depending on whether or not the count includes any of the bonus balls then it will need a different method.
 
Upvote 0
jasonb75 said:
There are ways but we would need a clearer outline of the required results in order to set up the formula.

This will give you "¥ 5" for 3 matches, "¥ 10" for 4, "¥ 20" for 5, or "¥ 0" for anything else.
Excel Formula: 
=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
We will need the specifics of etc to include anything else. Adding 6 or 7 matches is easy but if there are different results depending on whether or not the count includes any of the bonus balls then it will need a different method.
Click to expand...
Hi Jason, thank you for your help. you're right and it's a kinda lot. Below is a snippet of how the winnings are actually calculated.
HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
KLMNOP
26JACKPOT7 NUMBERS5 + 2●●●●●●●$1,000,000
272ND PLACE6 NUMBERS5 + 1●●●●●●¥500 000
283RD PLACE5 NUMBERS5●●●●●$10,000
294TH PLACE6 NUMBERS4 + 2●●●●●●$3,000
305TH PLACE5 NUMBERS4 + 1●●●●●$300
316TH PLACE5 NUMBERS3 + 2●●●●●$200
327TH PLACE4 NUMBERS4●●●●$100
338TH PLACE4 NUMBERS3 + 1 ●●●●$15
342 + 2●●●●$15
359TH PLACE3 NUMBERS3●●●$5
361 + 2●●●$5
372 + 1●●●$5
382 NUMBERS2●●$5
39
40●= REGULAR BALLS'
41●= BONUS BALLS'
LotteryTable


Hope this helps and you're able to help me out, please.
Thank you so much again.
 
Upvote 0
Hi Jason, I'm not sure if you would mind helping me out with another issue also. The formula you gave me is working fine in all cells except two and I can't figure out why. here's the snippet. In row 13 and 31 which has the same numbers, neither of the conditional formattings is taking effect. Well, that's not accurate. The conditional formatting to highlight numbers that match with the latest draw numbers seems to be working fine, but the conditional formatting to highlight the adjacent cell if it has 3 numbers or more isn't working, neither is the formula that you gave me to include the winning prize money. however, it's working everywhere else. I don't know what's happening with these two cells. i colored the adjacent cell with a red fill to point them out.
Do you think you can please help me?
HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
ABCDEFGHIJ
1LOTTERY TICKET PURCHASE HISTORY
2
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2TRY OUT AREA
52493234910¥ 5
6¥ 0
7¥ 5
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 5
911/22/2115679910¥ 5¥ 0
1015789712¥ 0
1119123334912¥ 5
1225689911¥ 5
13192630323369¥ 0HERE
14810152033712¥ 0
1541115171838¥ 0
16714182021611¥ 0
171017212324912¥ 0
181320242627312¥ 0
19162327293036¥ 0
20216171834410¥ 5
21510212531511¥ 0
22213143234212¥ 5
2318212223251112¥ 0
241318192732611¥ 0
25192123252729¥ 0
2611/24/211213152324611¥ 0
2752124313235¥ 0
28151619252916¥ 0
2967121729812¥ 0
302341026410¥ 5
31192630323369¥ 0HERE
32810152033712¥ 0
3315679910¥ 5
3415789212¥ 0
3519123334912¥ 5
3625689911¥ 5
3741115171838¥ 0
38714182021611¥ 0
391017212324912¥ 0
401320242627312¥ 0
41162327293036¥ 0
42619212830810¥ 0
4367131535712¥ 0
44510143134110¥ 0
45131827343578¥ 0
4621623293236¥ 0
472811141725¥ 0
48181923253449¥ 0
49214171935911¥ 0
50811181927910¥ 0
511415232530211¥ 0
52125111589¥ 0
5351224293328¥ 0
541611243448¥ 0
55789121948¥ 0
56112283233111¥ 0
575712141689¥ 0
584815172089¥ 0
5945681319¥ 0
604681320112¥ 0
6125681319¥ 0
625712132248¥ 0
63810151625712¥ 0
641213181928310¥ 0
65151621223116¥ 0
66181924273449¥ 0
67249101915¥ 5
6831017192956¥ 0
6981417222314¥ 0
703111428301112¥ 0
71172021243024¥ 0
72216171834410¥ 5
73510212531511¥ 0
74213143234212¥ 5
7518212223251112¥ 0
761318192732611¥ 0
77192123252729¥ 0
7811/27/2115679910¥ 5
7915789212¥ 0
8019123334912¥ 5
8125689911¥ 5
82216171834410¥ 5
83510212531511¥ 0
84213143234212¥ 5
8518212223251112¥ 0
861318192732611¥ 0
87192123252729¥ 0
8812/15/217912222968¥ 0
LotteryTable
Cell Formulas
RangeFormula
J5:J7J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
J8:J9J8=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B12:F12,$B$5:$F$5))+SUMPRODUCT(--(G12:H12=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
I9:I88I9=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
number07=tbllottery[BONUS 2]I9, J5
number08=tbllottery[BONUS 1]I9, J5
number09=tbllottery[NO 5]I9, J5
numberCheck=LotteryTable!$B9:INDEX(number09,COUNT(number09),1)I9, J5
numberCheck02=LotteryTable!$G9:INDEX(number08,COUNT(number08),1)I9, J5
numberCheck03=LotteryTable!$H9:INDEX(number07,COUNT(number07),1)I9, J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:H88Expression=AND(COUNTIF($G$5:$H$5,G9),COUNTIF(BONUSNUMBERCHECK,G9)=1)textNO
G9:H88Expression=COUNTIF($G$5:$H$5,G9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO


if I change the numbers of the latest draw so that there are 3 matches in these two cells the numbers are highlighted light green but then nothing happens in the winners' column and the prize money amount doesn't change. have a look.
HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
ABCDEFGHIJ
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2TRY OUT AREA
52493233910¥ 5
6¥ 0
7¥ 5
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 5
911/22/2115679910¥ 5¥ 0
1015789712¥ 0
1119123334912¥ 5
1225689911¥ 5
13192630323369¥ 0HERE
14810152033712¥ 0
1541115171838¥ 0
16714182021611¥ 0
171017212324912¥ 0
181320242627312¥ 0
19162327293036¥ 0
20216171834410¥ 0
21510212531511¥ 0
22213143234212¥ 0
2318212223251112¥ 0
241318192732611¥ 0
25192123252729¥ 0
2611/24/211213152324611¥ 0
2752124313235¥ 0
28151619252916¥ 0
2967121729812¥ 0
302341026410¥ 5
31192630323369¥ 0HERE
32810152033712¥ 0
LotteryTable
Cell Formulas
RangeFormula
J5:J7J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
J8:J9J8=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B12:F12,$B$5:$F$5))+SUMPRODUCT(--(G12:H12=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
I9:I32I9=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
number07=tbllottery[BONUS 2]I9, J5
number08=tbllottery[BONUS 1]I9, J5
number09=tbllottery[NO 5]I9, J5
numberCheck=LotteryTable!$B9:INDEX(number09,COUNT(number09),1)I9, J5
numberCheck02=LotteryTable!$G9:INDEX(number08,COUNT(number08),1)I9, J5
numberCheck03=LotteryTable!$H9:INDEX(number07,COUNT(number07),1)I9, J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:H88Expression=AND(COUNTIF($G$5:$H$5,G9),COUNTIF(BONUSNUMBERCHECK,G9)=1)textNO
G9:H88Expression=COUNTIF($G$5:$H$5,G9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO
 
Upvote 0
I should probably also mention that if there are 3 or more matches in $B$5:$F$5 however, it does change, but if the 3 matches are 2 from $B$5:$F$5 and 1 from $G$5:$F$5 or 1 from $B$5:$F$5 and 2 from $G$5:$F$5 then nothing happens, but if the order is exactly the same in the bonus group then it changes. Have a look at the snippet. In this one, it was highlighted and the prize money showed correctly because the bonus number has two matches in the exact same order, 6 and 9.
HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
ABCDEFGHIJ
1LOTTERY TICKET PURCHASE HISTORY
2
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2TRY OUT AREA
5249323469¥ 0
6¥ 0
7¥ 0
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 0
911/22/2115679910¥ 0¥ 5
1015789712¥ 0
1119123334912¥ 0
1225689911¥ 0
13192630323369¥ 5HERE
14810152033712¥ 0
1541115171838¥ 0
16714182021611¥ 0
171017212324912¥ 0
181320242627312¥ 0
19162327293036¥ 0
20216171834410¥ 0
21510212531511¥ 0
22213143234212¥ 5
2318212223251112¥ 0
241318192732611¥ 0
25192123252729¥ 0
2611/24/211213152324611¥ 0
2752124313235¥ 0
28151619252916¥ 0
2967121729812¥ 0
302341026410¥ 0
31192630323369¥ 5HERE
LotteryTable
Cell Formulas
RangeFormula
J5:J7J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
J8:J9J8=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B12:F12,$B$5:$F$5))+SUMPRODUCT(--(G12:H12=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
I9:I31I9=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
number07=tbllottery[BONUS 2]I9, J5
number08=tbllottery[BONUS 1]I9, J5
number09=tbllottery[NO 5]I9, J5
numberCheck=LotteryTable!$B9:INDEX(number09,COUNT(number09),1)I9, J5
numberCheck02=LotteryTable!$G9:INDEX(number08,COUNT(number08),1)I9, J5
numberCheck03=LotteryTable!$H9:INDEX(number07,COUNT(number07),1)I9, J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:H88Expression=AND(COUNTIF($G$5:$H$5,G9),COUNTIF(BONUSNUMBERCHECK,G9)=1)textNO
G9:H88Expression=COUNTIF($G$5:$H$5,G9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO


Here the two bonus numbers matches but it doesn't highlight and the prize money doesn't change but there are 3 matches in total.

HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
ABCDEFGHIJ
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2TRY OUT AREA
5249323496¥ 0
6¥ 0
7¥ 5
8DATENO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 5
911/22/2115679910¥ 0¥ 0
1015789712¥ 0
1119123334912¥ 5
1225689911¥ 5
13192630323369¥ 0HERE
LotteryTable
Cell Formulas
RangeFormula
J5:J7J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
J8:J9J8=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B12:F12,$B$5:$F$5))+SUMPRODUCT(--(G12:H12=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
I9:I13I9=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
number07=tbllottery[BONUS 2]I9, J5
number08=tbllottery[BONUS 1]I9, J5
number09=tbllottery[NO 5]I9, J5
numberCheck=LotteryTable!$B9:INDEX(number09,COUNT(number09),1)I9, J5
numberCheck02=LotteryTable!$G9:INDEX(number08,COUNT(number08),1)I9, J5
numberCheck03=LotteryTable!$H9:INDEX(number07,COUNT(number07),1)I9, J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:H88Expression=AND(COUNTIF($G$5:$H$5,G9),COUNTIF(BONUSNUMBERCHECK,G9)=1)textNO
G9:H88Expression=COUNTIF($G$5:$H$5,G9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO


Here again there are 3 matches in total but there are 2 from the regular numbers and 1 from the bonus so it doesn't change. but it changes for all the other numbers. I can't understand why.

HIGHLIGHT THE LATEST LOTTERY MATCHES.xlsx
BCDEFGHIJ
3LATEST DRAW
4NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2TRY OUT AREA
519493234910¥ 5
6¥ 0
7¥ 5
8NO 1NO 2NO 3NO 4NO 5BONUS 1BONUS 2WINNERS¥ 0
915679910¥ 5¥ 0
1015789712¥ 0
1119123334912¥ 5
1225689911¥ 0
13192630323369¥ 0HERE
LotteryTable
Cell Formulas
RangeFormula
J5:J7J5=IF(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3,"¥ 5","¥ 0")
J8:J9J8=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B12:F12,$B$5:$F$5))+SUMPRODUCT(--(G12:H12=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
I9:I13I9=IFERROR(CHOOSE(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+SUMPRODUCT(--(G9:H9=$G$5:$H$5))-2,"¥ 5","¥ 10","¥ 20"),"¥ 0")
Press CTRL+SHIFT+ENTER to enter array formulas.
Named Ranges
NameRefers ToCells
number07=tbllottery[BONUS 2]I9, J5
number08=tbllottery[BONUS 1]I9, J5
number09=tbllottery[NO 5]I9, J5
numberCheck=LotteryTable!$B9:INDEX(number09,COUNT(number09),1)I9, J5
numberCheck02=LotteryTable!$G9:INDEX(number08,COUNT(number08),1)I9, J5
numberCheck03=LotteryTable!$H9:INDEX(number07,COUNT(number07),1)I9, J5
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B9:F88Expression=AND(COUNTIF($B$5:$F$5,B9),COUNTIF(numberCheck,B9)=1)textNO
B9:F88Expression=COUNTIF($B$5:$F$5,B9)textNO
G9:H88Expression=AND(COUNTIF($G$5:$H$5,G9),COUNTIF(BONUSNUMBERCHECK,G9)=1)textNO
G9:H88Expression=COUNTIF($G$5:$H$5,G9)textNO
I9:I88Expression=SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))+COUNTIF(G9,$G$5)+COUNTIF(H9,$H$5)>=3textNO
 
Upvote 0
jasonb75 said:
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).
Click to expand...
HI JASON, I think you're right, it's only counting the numbers that are in exactly the same position in both tables. How should I correct that, please?
Thank you
 
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Solved
SUM COUNTIFS with AND + OR
Replies
3
Views
214
Fluff
Fluff
Asbestos_Jen
  • Question Question
Compare multiple parameters of samples to reference values (Excel 2007)
Replies
3
Views
413
Asbestos_Jen
Asbestos_Jen
C
  • Question Question
COUNTIF? COUNTIFS? SUMIF? SUM(IF)[CO...AARRGH SOMEONE PLEASE HELP!
Replies
9
Views
428
KRice
K
P
  • Question Question
Count names based on enrollment
Replies
2
Views
242
paulkc
P
I
  • Solved
Simple Countif producing wrong results
Replies
4
Views
304
Cubist
Cubist

Forum statistics

Threads
1,225,149
Messages
6,183,178
Members
453,151
Latest member
Lizamaison

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top