Help with sum and countif

Samgraphics · Jan 29, 2022

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

10

1

5

7

8

9

2

12

11

1

9

12

33

34

9

12

2

5

6

8

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 · Jan 31, 2022

Try asking one question at a time, when you ask too much people tend to skip over it.

Starting with post 17, is this something that you can use? Note that I've made changes to the table in the lower right corner. Also should the 2 number prize be for 2 normal numbers, 2 bonus balls, or any combination of 2 balls? I've set it up for 2 normal but it will need changing if it should be anything else.

Book1

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

2

4

9

32

34

9

10

6

7

8

DATE

NO 1

NO 2

NO 3

NO 4

NO 5

BONUS 1

BONUS 2

WINNERS

9

44522

1

5

6

7

9

10

5

10

1

5

7

8

9

7

12

0

11

1

9

12

33

34

9

12

5

12

2

5

6

8

9

11

5

13

19

26

30

32

33

6

9

0

14

8

10

15

20

33

7

12

0

15

4

11

15

17

18

3

8

0

16

7

14

18

20

21

6

11

0

17

10

17

21

23

24

9

12

0

18

13

20

24

26

27

3

12

0

19

16

23

27

29

30

3

6

0

20

2

16

17

18

34

4

10

5

21

5

10

21

25

31

5

11

0

22

2

13

14

32

34

2

12

5

23

18

21

22

23

25

11

12

0

24

13

18

19

27

32

6

11

0

25

19

21

23

25

27

2

9

0

26

44524

12

13

15

23

24

6

11

0

JACKPOT

7 NUMBERS

52

●●●●●

●●

1000000

27

5

21

24

31

32

3

5

0

2ND PLACE

6 NUMBERS

51

●●●●●

●

¥500 000

28

15

16

19

25

29

1

6

0

3RD PLACE

5 NUMBERS

50

●●●●●

10000

29

6

7

12

17

29

8

12

0

4TH PLACE

6 NUMBERS

42

●●●●

●●

3000

30

2

3

4

10

26

4

10

5

5TH PLACE

5 NUMBERS

41

●●●●

●

300

31

19

26

30

32

33

6

9

0

HERE

6TH PLACE

5 NUMBERS

32

●●●

●●

200

32

8

10

15

20

33

7

12

0

7TH PLACE

4 NUMBERS

40

●●●●

100

33

1

5

6

7

9

10

5

8TH PLACE

4 NUMBERS

31

●●●

●

15

34

1

5

7

8

9

2

12

0

22

●●

15

35

1

9

12

33

34

9

12

5

9TH PLACE

3 NUMBERS

30

●●●

5

36

2

5

6

8

9

11

5

12

●

●●

5

37

4

11

15

17

18

3

8

0

21

●●

●

5

38

7

14

18

20

21

6

11

0

2 NUMBERS

20

●●

5

39

10

17

21

23

24

9

12

0

40

13

20

24

26

27

3

12

0

●

= REGULAR BALLS'

41

16

23

27

29

30

3

6

0

●

= BONUS BALLS'

42

6

19

21

28

30

8

10

0

43

6

7

13

15

35

7

12

0

44

5

10

14

31

34

1

10

0

45

13

18

27

34

35

7

8

0

46

2

16

23

29

32

3

6

5

47

2

8

11

14

17

2

5

0

48

18

19

23

25

34

4

9

0

49

2

14

17

19

35

9

11

0

50

8

11

18

19

27

9

10

0

51

14

15

23

25

30

2

11

0

52

1

2

5

11

15

8

9

0

53

5

12

24

29

33

2

8

0

54

1

6

11

24

34

4

8

0

55

7

8

9

12

19

4

8

0

56

1

12

28

32

33

1

11

0

57

5

7

12

14

16

8

9

0

58

4

8

15

17

20

8

9

0

59

4

5

6

8

13

1

9

0

60

4

6

8

13

20

1

12

0

61

2

5

6

8

13

1

9

0

62

5

7

12

13

22

4

8

0

63

8

10

15

16

25

7

12

0

64

12

13

18

19

28

3

10

0

65

15

16

21

22

31

1

6

0

66

18

19

24

27

34

4

9

0

67

2

4

9

10

19

1

5

68

3

10

17

19

29

5

6

0

69

8

14

17

22

23

1

4

0

70

3

11

14

28

30

11

12

0

71

17

20

21

24

30

2

4

0

72

2

16

17

18

34

4

10

5

73

5

10

21

25

31

5

11

0

74

2

13

14

32

34

2

12

5

75

18

21

22

23

25

11

12

0

76

13

18

19

27

32

6

11

0

77

19

21

23

25

27

2

9

0

78

44527

1

5

6

7

9

10

5

79

1

5

7

8

9

2

12

0

80

1

9

12

33

34

9

12

5

81

2

5

6

8

9

11

5

82

2

16

17

18

34

4

10

5

83

5

10

21

25

31

5

11

0

84

2

13

14

32

34

2

12

5

85

18

21

22

23

25

11

12

0

86

13

18

19

27

32

6

11

0

87

19

21

23

25

27

2

9

0

88

44545

7

9

12

22

29

6

8

0

Sheet1

Cell Formulas
Range	Formula
I9:I88	I9	=IFERROR(VLOOKUP(--(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))&SUMPRODUCT(--(G9:H9=TRANSPOSE($G$5:$H$5)))),$M$26:$P$38,4,0),0)

Samgraphics · Jan 31, 2022

jasonb75 said:
Try asking one question at a time, when you ask too much people tend to skip over it.

Starting with post 17, is this something that you can use? Note that I've made changes to the table in the lower right corner. Also should the 2 number prize be for 2 normal numbers, 2 bonus balls, or any combination of 2 balls? I've set it up for 2 normal but it will need changing if it should be anything else.

Book1
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 2 4 9 32 34 9 10
6
7
8 DATE NO 1 NO 2 NO 3 NO 4 NO 5 BONUS 1 BONUS 2 WINNERS
9 44522 1 5 6 7 9 9 10 5
10 1 5 7 8 9 7 12 0
11 1 9 12 33 34 9 12 5
12 2 5 6 8 9 9 11 5
13 19 26 30 32 33 6 9 0
14 8 10 15 20 33 7 12 0
15 4 11 15 17 18 3 8 0
16 7 14 18 20 21 6 11 0
17 10 17 21 23 24 9 12 0
18 13 20 24 26 27 3 12 0
19 16 23 27 29 30 3 6 0
20 2 16 17 18 34 4 10 5
21 5 10 21 25 31 5 11 0
22 2 13 14 32 34 2 12 5
23 18 21 22 23 25 11 12 0
24 13 18 19 27 32 6 11 0
25 19 21 23 25 27 2 9 0
26 44524 12 13 15 23 24 6 11 0 JACKPOT 7 NUMBERS 52 ●●●●● ●● 1000000
27 5 21 24 31 32 3 5 0 2ND PLACE 6 NUMBERS 51 ●●●●● ● ¥500 000
28 15 16 19 25 29 1 6 0 3RD PLACE 5 NUMBERS 50 ●●●●● 10000
29 6 7 12 17 29 8 12 0 4TH PLACE 6 NUMBERS 42 ●●●● ●● 3000
30 2 3 4 10 26 4 10 5 5TH PLACE 5 NUMBERS 41 ●●●● ● 300
31 19 26 30 32 33 6 9 0 HERE 6TH PLACE 5 NUMBERS 32 ●●● ●● 200
32 8 10 15 20 33 7 12 0 7TH PLACE 4 NUMBERS 40 ●●●● 100
33 1 5 6 7 9 9 10 5 8TH PLACE 4 NUMBERS 31 ●●● ● 15
34 1 5 7 8 9 2 12 0 22 ●● ●● 15
35 1 9 12 33 34 9 12 5 9TH PLACE 3 NUMBERS 30 ●●● 5
36 2 5 6 8 9 9 11 5 12 ● ●● 5
37 4 11 15 17 18 3 8 0 21 ●● ● 5
38 7 14 18 20 21 6 11 0 2 NUMBERS 20 ●● 5
39 10 17 21 23 24 9 12 0
40 13 20 24 26 27 3 12 0 ● = REGULAR BALLS'
41 16 23 27 29 30 3 6 0 ● = BONUS BALLS'
42 6 19 21 28 30 8 10 0
43 6 7 13 15 35 7 12 0
44 5 10 14 31 34 1 10 0
45 13 18 27 34 35 7 8 0
46 2 16 23 29 32 3 6 5
47 2 8 11 14 17 2 5 0
48 18 19 23 25 34 4 9 0
49 2 14 17 19 35 9 11 0
50 8 11 18 19 27 9 10 0
51 14 15 23 25 30 2 11 0
52 1 2 5 11 15 8 9 0
53 5 12 24 29 33 2 8 0
54 1 6 11 24 34 4 8 0
55 7 8 9 12 19 4 8 0
56 1 12 28 32 33 1 11 0
57 5 7 12 14 16 8 9 0
58 4 8 15 17 20 8 9 0
59 4 5 6 8 13 1 9 0
60 4 6 8 13 20 1 12 0
61 2 5 6 8 13 1 9 0
62 5 7 12 13 22 4 8 0
63 8 10 15 16 25 7 12 0
64 12 13 18 19 28 3 10 0
65 15 16 21 22 31 1 6 0
66 18 19 24 27 34 4 9 0
67 2 4 9 10 19 1 5 5
68 3 10 17 19 29 5 6 0
69 8 14 17 22 23 1 4 0
70 3 11 14 28 30 11 12 0
71 17 20 21 24 30 2 4 0
72 2 16 17 18 34 4 10 5
73 5 10 21 25 31 5 11 0
74 2 13 14 32 34 2 12 5
75 18 21 22 23 25 11 12 0
76 13 18 19 27 32 6 11 0
77 19 21 23 25 27 2 9 0
78 44527 1 5 6 7 9 9 10 5
79 1 5 7 8 9 2 12 0
80 1 9 12 33 34 9 12 5
81 2 5 6 8 9 9 11 5
82 2 16 17 18 34 4 10 5
83 5 10 21 25 31 5 11 0
84 2 13 14 32 34 2 12 5
85 18 21 22 23 25 11 12 0
86 13 18 19 27 32 6 11 0
87 19 21 23 25 27 2 9 0
88 44545 7 9 12 22 29 6 8 0
Sheet1
Cell Formulas
Range Formula
I9:I88 I9 =IFERROR(VLOOKUP(--(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))&SUMPRODUCT(--(G9:H9=TRANSPOSE($G$5:$H$5)))),$M$26:$P$38,4,0),0)

Hi Jason,
Thank you so much for your help, you are right and I'm sorry about that. I just got carried away really, trying to explain myself properly and then I saw some replies before others and just wanted to make sure I wasn't being rude by not replying or explaining myself clearly, but I'm terribly sorry, and thanks for calling me out on it. When I win, I won't forget all the help I received from everyone.
Thank you again for your time and helpful solutions.
Thank you so much

Samgraphics · Jan 31, 2022

jasonb75 said:
Try asking one question at a time, when you ask too much people tend to skip over it.

Starting with post 17, is this something that you can use? Note that I've made changes to the table in the lower right corner. Also should the 2 number prize be for 2 normal numbers, 2 bonus balls, or any combination of 2 balls? I've set it up for 2 normal but it will need changing if it should be anything else.

Book1
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 2 4 9 32 34 9 10
6
7
8 DATE NO 1 NO 2 NO 3 NO 4 NO 5 BONUS 1 BONUS 2 WINNERS
9 44522 1 5 6 7 9 9 10 5
10 1 5 7 8 9 7 12 0
11 1 9 12 33 34 9 12 5
12 2 5 6 8 9 9 11 5
13 19 26 30 32 33 6 9 0
14 8 10 15 20 33 7 12 0
15 4 11 15 17 18 3 8 0
16 7 14 18 20 21 6 11 0
17 10 17 21 23 24 9 12 0
18 13 20 24 26 27 3 12 0
19 16 23 27 29 30 3 6 0
20 2 16 17 18 34 4 10 5
21 5 10 21 25 31 5 11 0
22 2 13 14 32 34 2 12 5
23 18 21 22 23 25 11 12 0
24 13 18 19 27 32 6 11 0
25 19 21 23 25 27 2 9 0
26 44524 12 13 15 23 24 6 11 0 JACKPOT 7 NUMBERS 52 ●●●●● ●● 1000000
27 5 21 24 31 32 3 5 0 2ND PLACE 6 NUMBERS 51 ●●●●● ● ¥500 000
28 15 16 19 25 29 1 6 0 3RD PLACE 5 NUMBERS 50 ●●●●● 10000
29 6 7 12 17 29 8 12 0 4TH PLACE 6 NUMBERS 42 ●●●● ●● 3000
30 2 3 4 10 26 4 10 5 5TH PLACE 5 NUMBERS 41 ●●●● ● 300
31 19 26 30 32 33 6 9 0 HERE 6TH PLACE 5 NUMBERS 32 ●●● ●● 200
32 8 10 15 20 33 7 12 0 7TH PLACE 4 NUMBERS 40 ●●●● 100
33 1 5 6 7 9 9 10 5 8TH PLACE 4 NUMBERS 31 ●●● ● 15
34 1 5 7 8 9 2 12 0 22 ●● ●● 15
35 1 9 12 33 34 9 12 5 9TH PLACE 3 NUMBERS 30 ●●● 5
36 2 5 6 8 9 9 11 5 12 ● ●● 5
37 4 11 15 17 18 3 8 0 21 ●● ● 5
38 7 14 18 20 21 6 11 0 2 NUMBERS 20 ●● 5
39 10 17 21 23 24 9 12 0
40 13 20 24 26 27 3 12 0 ● = REGULAR BALLS'
41 16 23 27 29 30 3 6 0 ● = BONUS BALLS'
42 6 19 21 28 30 8 10 0
43 6 7 13 15 35 7 12 0
44 5 10 14 31 34 1 10 0
45 13 18 27 34 35 7 8 0
46 2 16 23 29 32 3 6 5
47 2 8 11 14 17 2 5 0
48 18 19 23 25 34 4 9 0
49 2 14 17 19 35 9 11 0
50 8 11 18 19 27 9 10 0
51 14 15 23 25 30 2 11 0
52 1 2 5 11 15 8 9 0
53 5 12 24 29 33 2 8 0
54 1 6 11 24 34 4 8 0
55 7 8 9 12 19 4 8 0
56 1 12 28 32 33 1 11 0
57 5 7 12 14 16 8 9 0
58 4 8 15 17 20 8 9 0
59 4 5 6 8 13 1 9 0
60 4 6 8 13 20 1 12 0
61 2 5 6 8 13 1 9 0
62 5 7 12 13 22 4 8 0
63 8 10 15 16 25 7 12 0
64 12 13 18 19 28 3 10 0
65 15 16 21 22 31 1 6 0
66 18 19 24 27 34 4 9 0
67 2 4 9 10 19 1 5 5
68 3 10 17 19 29 5 6 0
69 8 14 17 22 23 1 4 0
70 3 11 14 28 30 11 12 0
71 17 20 21 24 30 2 4 0
72 2 16 17 18 34 4 10 5
73 5 10 21 25 31 5 11 0
74 2 13 14 32 34 2 12 5
75 18 21 22 23 25 11 12 0
76 13 18 19 27 32 6 11 0
77 19 21 23 25 27 2 9 0
78 44527 1 5 6 7 9 9 10 5
79 1 5 7 8 9 2 12 0
80 1 9 12 33 34 9 12 5
81 2 5 6 8 9 9 11 5
82 2 16 17 18 34 4 10 5
83 5 10 21 25 31 5 11 0
84 2 13 14 32 34 2 12 5
85 18 21 22 23 25 11 12 0
86 13 18 19 27 32 6 11 0
87 19 21 23 25 27 2 9 0
88 44545 7 9 12 22 29 6 8 0
Sheet1
Cell Formulas
Range Formula
I9:I88 I9 =IFERROR(VLOOKUP(--(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))&SUMPRODUCT(--(G9:H9=TRANSPOSE($G$5:$H$5)))),$M$26:$P$38,4,0),0)

Hi sorry again, I didn't answer your question, the last "2 numbers" ball should be for two bonus balls. The first question is, "Is this something I can use?" I'm not sure what you mean by that question.

Thank you again.

jasonb75 · Feb 1, 2022

Samgraphics said:
The first question is, "Is this something I can use?" I'm not sure what you mean by that question.

If you look at the sheet in my reply you will see that I've changed the formula so that it shows a 2 digit number for matching balls with the first digit being for normal numbers and the second for bonus numbers, I've also made some small changes to the prize list table to match that. If you can make that method fit into what you are doing then it will be the simplest way to do it. With the original formula it would need to be about 10 timer longer to include all possible results.

Samgraphics · Feb 1, 2022

jasonb75 said:
If you look at the sheet in my reply you will see that I've changed the formula so that it shows a 2 digit number for matching balls with the first digit being for normal numbers and the second for bonus numbers, I've also made some small changes to the prize list table to match that. If you can make that method fit into what you are doing then it will be the simplest way to do it. With the original formula it would need to be about 10 timer longer to include all possible results.

Hi Jason, thank you so much for explaining that to me. lol so I did spend a lot of time analyzing the formula, but my Excel skills are so poor I couldn't piece it together but what you said helped. So I did notice that you changed the numbers from 3 + 2 to 32 but I thought you were doing that so excel would be able to recognize them as a whole integer and be able to reference it. I was wrong! once I read your email I was able to piece the rest together and now have a working formula that's giving me the results I need. Thank you so much. here's what I did. first, because excel removes the first zero in a number if it's not a decimal number and I needed to make the 20 into a 02 so that the second digit would match the balls in the bonus area so, I had to write it as '02 then I placed a '--' in front of the range in the formula so that Excel could convert it back into a number and voila, it worked. I don't know if that's the most efficient way to do it, but it's working and producing the right results so I'm ecstatic.
Thank you so much I won't forget all your help and time.

=IFERROR(VLOOKUP(--(SUMPRODUCT(COUNTIF(B9:F9,$B$5:$F$5))&SUMPRODUCT(--(G9:H9=TRANSPOSE($G$5:$H$5)))),--$M$26:$P$38,4,0),0)

ps, I still haven't figured out what other changes you made in the prize list table, but everything's working fine now. I don't know if you're referring to removing the dollar signs and the spaces but it's really working the way I need it to, so I'm happy.
Thank you thank you thank you.

Help with sum and countif

Samgraphics

Board Regular

jasonb75

Well-known Member

Excel Facts

Samgraphics

Board Regular

Samgraphics

Board Regular

jasonb75

Well-known Member

Samgraphics

Board Regular

Similar threads

Forum statistics

Share this page

Help with sum and countif

Samgraphics

Board Regular

jasonb75

Well-known Member

Excel Facts

Samgraphics

Board Regular

Samgraphics

Board Regular

jasonb75

Well-known Member

Samgraphics

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock