excel match feature

[simon31]

Hello there,

I use countif formula to find out how many times in one year 8 numbers have matched.

Usually 8 numbers match only once a year or maybe not. prize money for 8 numbers is 10k as above.

But there many times 4,5 or 6 numbers match.

For example to find out how many times 4numbers have matched, I have to scroll through for the entire year to find that out and that takes a very long time.

1) What formula can I use to find out how many times 4 numbers have matched so I can get the answer in one go.?

Each 4 numbers matched has prize money of $2.

2) what formula can I use to find out the total dollar value of the 4 numbers matched ?

https://filebin.net/kkiwhh8z3nsxif74

Thanks
simon

 

[AlanY]

Hello there,

I use countif formula to find out how many times in one year 8 numbers have matched.

what is the countif formula used for above?
should be able to use the same for 4, 6 numbers etc

 

[simon31]

the countif formula was countif(x:x,8) but that applies only to 8 hit numbers.

But if 7 numbers have hit instead of 8numbers, I don't know what formula to use if 7 numbers out of 8 have been hit.

 

[AlanY]

1) are the numbers in X:X generated by formula? If so, what is it?

2) shouldn't =countif(X:X,7) give you the number of 7 been hit?

 

[simon31]

I should have rephrased my question but there is no option to edit once posted

13 14 22 36 55 66 77 78

I have chosen the above 8 numbers and wish to do the following.

1) find out how many times there were 8 hits,7 and 6 hits, I use countif for that


2) When I check for 6 hits of the above 8 numbers, I get about 12 times as the answer with the countif formula


3) I wish to know now which of 6 hits out of the above 8 numbers came 12 times


There could be a mix of those 6 hits that came 12 times
At the moment I spend hours to find that out .
Is there a way I can quick filter that list or use a formula for that.?

 

[simon31]

forgot to add that I wish to know which of the 6 hits out of the above 8 numbers came 12 times in one year

Looking for a filter feature or formula for a quick results

 

[AlanY]

I've created a dummy file for testing, Column X works out the number of matches of the 8 nos in O2:V2, and you can use a filter in Row 1 to filtered out the no of hits

https://drive.google.com/file/d/1WNX1ovXxSCRoqmlufBsneqY7vgWzpSUq/view?usp=sharing

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1
2	Week														13	14	22	36	55	66	77	78		No of hits
3	1		2	7	10	15	16	20	23	27	31	34	37	39	40	45	49	50	52	55	58	60		1
4	2		3	6	10	11	15	16	19	23	25	30	31	32	36	39	40	42	45	47	49	51		1
5	3		5	9	10	13	14	17	18	21	25	29	31	32	37	38	41	43	45	48	50	53		2
6	4		2	5	6	7	12	14	17	21	23	28	30	33	36	38	40	44	47	50	54	57		2
7	5		6	10	13	18	22	25	26	30	34	38	42	43	45	49	54	57	58	63	68	69		2
8	6		8	13	15	19	22	24	26	29	32	37	38	42	47	50	52	53	55	57	62	67		3
9	7		4	8	9	14	15	16	20	21	22	27	28	29	30	32	37	41	45	48	51	55		3
10	8		5	7	10	14	18	19	21	23	27	30	33	35	37	39	44	47	49	51	52	55		2
11	9		1	2	5	10	13	17	19	20	21	25	26	29	31	32	36	38	42	44	46	51		2
12	10		8	13	18	20	25	29	31	34	39	40	44	45	48	49	52	53	58	62	66	69		2
13	11		10	11	15	17	21	23	26	28	32	35	36	41	45	48	52	55	57	58	61	63		2
14	12		8	11	16	19	21	23	28	33	37	39	43	44	47	50	55	59	62	63	67	69		1
15	13		9	13	15	16	18	19	23	27	31	35	37	40	42	43	45	46	48	49	51	52		1
16	14		3	5	10	11	12	13	16	17	21	25	27	32	36	40	42	46	51	55	59	61		3
17	15		4	8	10	13	14	19	24	26	30	31	36	40	44	47	49	51	55	60	63	64		4
18	16		9	13	17	18	23	25	26	29	31	33	37	41	42	47	52	55	56	58	59	62		2
19	17		10	15	16	19	22	27	30	33	34	35	37	39	44	48	49	53	57	58	61	65		1
20	18		9	11	16	17	19	20	25	27	32	37	39	43	44	45	47	52	53	56	59	60		0
21	19		10	14	19	22	26	27	28	29	32	37	41	43	44	47	48	52	57	61	64	69		2
22	20		5	10	15	20	25	27	32	37	40	44	45	47	50	55	56	60	62	67	71	76		1
23	21		3	7	11	14	17	21	26	27	29	33	34	35	39	44	47	51	55	58	61	63		2
24	22		5	6	7	12	16	19	20	22	26	29	34	38	40	41	43	47	52	54	56	61		1
25	23		4	7	9	14	19	22	23	26	30	34	37	42	47	48	52	57	58	62	64	68		2
26	24		2	3	4	9	14	15	16	21	26	28	32	35	38	41	42	45	47	50	51	55		2
27	25		8	10	12	13	14	15	17	18	20	23	26	31	34	39	44	48	52	53	55	58		3
28	26		8	10	13	14	17	18	19	24	28	31	36	38	39	41	46	51	55	59	62	65		4
29	27		7	9	10	13	14	17	20	22	27	28	29	32	34	35	36	40	44	47	50	53		4
30	28		9	10	14	15	19	23	28	32	33	38	41	43	48	51	54	57	62	67	71	72		1
31	29		9	12	13	14	16	19	20	22	26	29	32	33	36	40	42	44	45	47	52	53		4
32	30		8	13	16	18	21	24	29	30	31	32	35	40	43	47	51	53	58	60	61	65		1
33	31		5	9	10	12	16	17	18	22	26	31	35	37	40	44	47	52	53	54	56	57		1
34	32		2	6	10	15	20	24	27	28	32	36	37	39	44	47	50	54	58	59	64	65		1
35	33		2	4	8	11	14	16	19	23	28	31	36	39	42	45	49	51	56	60	64	67		2
36	34		8	13	15	18	20	23	28	32	35	36	38	39	44	45	46	49	54	56	57	62		2
37	35		9	14	16	18	19	24	25	28	32	33	35	40	44	49	50	54	56	58	63	64		1
38	36		4	6	8	11	14	18	23	27	28	33	38	40	44	45	47	51	54	55	58	62		2
39	37		1	2	4	8	11	13	14	15	16	21	25	27	32	33	37	40	43	47	52	56		2
40	38		10	12	16	21	24	29	31	32	33	37	38	39	44	49	52	55	56	60	61	62		1
41	39		6	9	13	16	19	21	23	25	26	27	32	37	41	43	46	47	51	55	59	64		2
42	40		2	7	8	9	13	17	18	22	24	28	33	34	39	40	41	42	45	47	48	51		2
43	41		5	10	15	19	23	27	28	32	37	42	46	47	49	54	57	60	64	68	70	72		0
44	42		1	5	8	10	13	18	19	24	28	32	35	38	39	40	44	46	50	55	60	61		2
45	43		2	4	8	11	12	16	20	22	25	28	29	34	37	42	45	47	49	52	55	59		2
46	44		1	2	5	6	10	12	13	15	18	23	27	30	33	38	42	46	50	55	59	61		2
47	45		9	11	13	16	18	20	25	30	34	35	39	42	47	51	55	56	59	64	66	67		3
48	46		2	7	12	15	19	22	25	28	31	33	35	40	44	49	53	57	58	60	63	68		1
49	47		4	9	12	15	19	20	24	29	32	33	38	42	44	45	50	52	56	58	59	63		0
50	48		4	6	8	13	16	20	25	26	29	34	36	41	44	47	49	51	53	56	57	59		2
51	49		8	9	14	17	20	24	29	34	35	40	42	44	46	47	48	49	51	55	59	61		2
52	50		7	11	13	16	21	26	30	32	33	35	38	40	44	49	53	54	57	60	62	66		2
53	51		10	15	17	18	19	21	26	31	35	39	42	45	46	50	55	59	62	67	69	70		1
54	52		8	12	14	19	20	22	26	31	34	39	43	45	49	50	55	59	62	64	65	70		3
Sheet1

Cell Formulas
Range	Formula
X3	=SUMPRODUCT(--(ISNUMBER(MATCH(O$2:V$2,$C3:$V3,0))))

 

[simon31]

I went through your dummy file.

I am confused here.

I see in the table that Row 3 has 20 numbers of week 1

But there are 4 keno draws per day 10 am , 2pm, 3pm and 6pm.

So I needed to see 4 rows for the 4 times.

example
[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]Date
[/TD]
[TD]Time
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]14
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[TD]20
[/TD]
[/TR]
[TR]
[TD]1 June 19
[/TD]
[TD]10am
[/TD]
[TD]5
[/TD]
[TD]9
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[TD]13
[/TD]
[TD]21
[/TD]
[TD]31
[/TD]
[TD]33
[/TD]
[TD]38
[/TD]
[TD]43
[/TD]
[TD]46
[/TD]
[TD]51
[/TD]
[TD]52
[/TD]
[TD]53
[/TD]
[TD]55
[/TD]
[TD]61
[/TD]
[TD]62
[/TD]
[TD]74
[/TD]
[TD]76
[/TD]
[TD]79
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]1pm
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]7
[/TD]
[TD]11
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]26
[/TD]
[TD]27
[/TD]
[TD]29
[/TD]
[TD]30
[/TD]
[TD]38
[/TD]
[TD]39
[/TD]
[TD]40
[/TD]
[TD]46
[/TD]
[TD]54
[/TD]
[TD]58
[/TD]
[TD]63
[/TD]
[TD]65
[/TD]
[TD]71
[/TD]
[TD]73
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]3pm
[/TD]
[TD]3
[/TD]
[TD]5
[/TD]
[TD]11
[/TD]
[TD]15
[/TD]
[TD]17
[/TD]
[TD]20
[/TD]
[TD]21
[/TD]
[TD]23
[/TD]
[TD]24
[/TD]
[TD]26
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]36
[/TD]
[TD]38
[/TD]
[TD]42
[/TD]
[TD]53
[/TD]
[TD]55
[/TD]
[TD]68
[/TD]
[TD]74
[/TD]
[TD]76
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6pm
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]10
[/TD]
[TD]12
[/TD]
[TD]17
[/TD]
[TD]20
[/TD]
[TD]25
[/TD]
[TD]30
[/TD]
[TD]32
[/TD]
[TD]35
[/TD]
[TD]38
[/TD]
[TD]51
[/TD]
[TD]53
[/TD]
[TD]56
[/TD]
[TD]59
[/TD]
[TD]64
[/TD]
[TD]68
[/TD]
[TD]75
[/TD]
[TD]78
[/TD]
[/TR]
</tbody>[/TABLE]










So I do need to see dates and the 4 times

And the no of hits could then be listed in column X.

Now if you created a formula then I should be seeing which dates those hits occured and which times.

 

[AlanY]

so, you want the no of hits of the total of 4 draws of the day?

e.g on June 19, no of matches in 1st draw = 1, 2nd = 1, 3rd =2 and 4th =2. then you want June 19 shows 6, is that right?

 

[simon31]

I wanted 10am draw to show how many numbers hits, same way for 1pm, 3pm and 6pm separately in its own row. The reason for that is I want an opportunity to filter for example only 10 am draw along with its corresponding date over a period of one year. And when I filter 10 am draw , all other numbers in the chosen period should get blanked out so I have a clear view of the 10 am draw hits over the one year period.

I should have the option to place any 8 numbers (between 1 and 80) on the first row as you did in my previous example.