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]

Re: excel filter - formula

the options for the filter in Cell K1 is based on to the results from the selected date/draw range, e.g. option 3 is not available from the drop down is because there isn't a 3 Nos set match in that period.

 

[simon31]

Re: excel filter - formula

I will try it again and see if 43 matches come again versus 12 matches previous time for me

 

[simon31]

Re: excel filter - formula

As you see above I got only 12 results.

Your table below has 43 results.

I followed your steps correctly

So I don't understand why the disrepancy between the results I got 12 results and your step got 43 results as above

 

[AlanY]

Re: excel filter - formula

could you post a screen for the front page selections, date/draws etc to yield the 12 results, or share the excel file so that i can have a look?

 

[simon31]

Re: excel filter - formula

ok sure. I will read your steps again carefully and then post the snapshots

 

[simon31]

Re: excel filter - formula

Step 1

Step 2

Step 3

I have carefully followed the first 3 steps and posted the snapshots above as requested

 

[AlanY]

Re: excel filter - formula

this is weird! I can't produce that 12 lines of results from the same date/draw as yours.

this is the file I'm testing, should be the same as yours, could you try to see if you can produce the same result with this

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

 

[simon31]

Re: excel filter - formula

I will try that again with your new file.

 

[simon31]

Re: excel filter - formula

Step1

Step2

step 3

I have tried your new file , but ended with the same 12 results

 

[AlanY]

Re: excel filter - formula

i don't understand it, at all.
from step 2 above, Cell F5 is 68 same as what I got.
but on the result page I got almost 600 lines of 2 matches or more, 11 from yours is just too small considering the number of draws included.

could you do me a favour, save the file after step 2 above (i.e. with the 12 lines of result) and share it so that I can have a look please