# 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

### Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

#### 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,102,782
Messages
5,488,850
Members
407,658
Latest member
Arias610

### This Week's Hot Topics

• Timer in VBA - Stop, Start, Pause and Reset
[CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
• how to updates multiple rows in muliselect listbox
Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
• Delete Row from Table
I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
• Assigning to a variable
I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
• Way to verify information
Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
• Active Cell Address – Inactive Sheet
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...