Page 1 of 13 12311 ... LastLast
Results 1 to 10 of 122

Thread: excel match feature
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default excel match feature

    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

  2. #2
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    Quote Originally Posted by simon31 View Post
    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

  3. #3
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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.

  4. #4
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    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?

  5. #5
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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.?

  6. #6
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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

  7. #7
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    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/1WNX...ew?usp=sharing

    ABCDEFGHIJKLMNOPQRSTUVWX
    1
    2Week1314223655667778No of hits
    31271015162023273134373940454950525558601
    42361011151619232530313236394042454749511
    53591013141718212529313237384143454850532
    642567121417212328303336384044475054572
    756101318222526303438424345495457586368692
    868131519222426293237384247505253555762673
    9748914151620212227282930323741454851553
    108571014181921232730333537394447495152552
    11912510131719202125262931323638424446512
    12108131820252931343940444548495253586266692
    131110111517212326283235364145485255575861632
    14128111619212328333739434447505559626367691
    15139131516181923273135374042434546484951521
    1614351011121316172125273236404246515559613
    1715481013141924263031364044474951556063644
    18169131718232526293133374142475255565859622
    191710151619222730333435373944484953575861651
    20189111617192025273237394344454752535659600
    211910141922262728293237414344474852576164692
    22205101520252732374044454750555660626771761
    2321371114172126272933343539444751555861632
    242256712161920222629343840414347525456611
    252347914192223263034374247485257586264682
    26242349141516212628323538414245475051552
    27258101213141517182023263134394448525355583
    28268101314171819242831363839414651555962654
    2927791013141720222728293234353640444750534
    30289101415192328323338414348515457626771721
    31299121314161920222629323336404244454752534
    32308131618212429303132354043475153586061651
    3331591012161718222631353740444752535456571
    3432261015202427283236373944475054585964651
    353324811141619232831363942454951566064672
    36348131518202328323536383944454649545657622
    37359141618192425283233354044495054565863641
    383646811141823272833384044454751545558622
    39371248111314151621252732333740434752562
    403810121621242931323337383944495255566061621
    4139691316192123252627323741434647515559642
    42402789131718222428333439404142454748512
    43415101519232728323742464749545760646870720
    444215810131819242832353839404446505560612
    454324811121620222528293437424547495255592
    46441256101213151823273033384246505559612
    47459111316182025303435394247515556596466673
    4846271215192225283133354044495357586063681
    4947491215192024293233384244455052565859630
    504846813162025262934364144474951535657592
    5149891417202429343540424446474849515559612
    52507111316212630323335384044495354576062662
    535110151718192126313539424546505559626769701
    54528121419202226313439434549505559626465703

    Sheet1



    Worksheet Formulas
    CellFormula
    X3=SUMPRODUCT(--(ISNUMBER(MATCH(O$2:V$2,$C3:$V3,0))))

    Last edited by AlanY; Jun 4th, 2019 at 02:46 AM.

  8. #8
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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










    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.

  9. #9
    Board Regular
    Join Date
    Oct 2014
    Location
    UK
    Posts
    3,826
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)

    Default Re: excel match feature

    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?

  10. #10
    Board Regular
    Join Date
    May 2019
    Posts
    61
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: excel match feature

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •