# excel match feature

#### simon31

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Well-known Member
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

##### Board Regular
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

##### Board Regular
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

##### Well-known Member
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

Last edited:

#### simon31

##### Board Regular
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
 Date Time 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 1 June 19 10am 5 9 11 12 13 21 31 33 38 43 46 51 52 53 55 61 62 74 76 79 1pm 2 3 7 11 15 16 26 27 29 30 38 39 40 46 54 58 63 65 71 73 3pm 3 5 11 15 17 20 21 23 24 26 30 31 36 38 42 53 55 68 74 76 6pm 1 2 3 10 12 17 20 25 30 32 35 38 51 53 56 59 64 68 75 78

<tbody>
</tbody>

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

##### Well-known Member
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

##### Board Regular
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.

1,082,316
Messages
5,364,483
Members
400,802
Latest member
RichBRich

### This Week's Hot Topics

• populate from drop list with multiple tables
Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
• Find list of words from sheet2 in sheet1 before a comma and extract text vba
Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
• Dynamic Formula entry - VBA code sought
Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...