Welcome Guest
Log In | Register )
You last visited December 10, 2016, 7:11 pm
All times shown are
Eastern Time (GMT-5:00)

Need an excel formula

Topic closed. 16 replies. Last post 3 years ago by omiller315.

Page 1 of 2
51
PrintE-mailLink
MillionsWanted's avatar - 24Qa6LT

Norway
Member #9517
December 10, 2004
1272 Posts
Online
Posted: January 11, 2014, 8:36 am - IP Logged

I need to count how many times i find the same numbers in two rows:

Row 1:

1

2

3

4

5

Row 2:

1

3

5

8

9

=(Formula) = 3

Thankful for any help.

    SergeM's avatar - slow icon.png
    Economy class
    Belgium
    Member #123700
    February 27, 2012
    4035 Posts
    Offline
    Posted: January 11, 2014, 8:02 pm - IP Logged

    You need to write a matrix function for this.

      Avatar
      New Member

      United States
      Member #24024
      October 19, 2005
      1 Posts
      Offline
      Posted: January 11, 2014, 11:05 pm - IP Logged

      You may use the countif formula.

      =COUNTIF(a1:g1,3)+COUNTIF(a5:g5,3)

        MillionsWanted's avatar - 24Qa6LT

        Norway
        Member #9517
        December 10, 2004
        1272 Posts
        Online
        Posted: January 12, 2014, 6:22 am - IP Logged

        Thanks, but not quite what I am looking for.

        Let me add: The number 3 result in my example is because it was three numbers which appeared in both rows/columns, 1, 3 and 5.

          SergeM's avatar - slow icon.png
          Economy class
          Belgium
          Member #123700
          February 27, 2012
          4035 Posts
          Offline
          Posted: January 12, 2014, 8:07 am - IP Logged

          <?"=Score('row 1';'row 2')"/?>

            MillionsWanted's avatar - 24Qa6LT

            Norway
            Member #9517
            December 10, 2004
            1272 Posts
            Online
            Posted: January 12, 2014, 3:23 pm - IP Logged

            Thank you to anyone who have tried to help including those who sent me messages.

            I found this solution on a website:

            =SUMPRODUCT(ISNUMBER(MATCH($D$16:$W$16;$D$17:$W$17;0))+0)

            It's supposed to check for occurences of same numbers between the drawn numbers and the "predicted" numbers.

              SergeM's avatar - slow icon.png
              Economy class
              Belgium
              Member #123700
              February 27, 2012
              4035 Posts
              Offline
              Posted: January 12, 2014, 8:40 pm - IP Logged

              On which dumb fooling site have you found that stuff? Puke

              The idea of using sumproduct() is good, but the rest is bad.

                SergeM's avatar - slow icon.png
                Economy class
                Belgium
                Member #123700
                February 27, 2012
                4035 Posts
                Offline
                Posted: January 12, 2014, 8:50 pm - IP Logged
                424
                224
                2Score boxed
                123
                132
                1Score straight
                234
                154
                1Score lotto
                  MillionsWanted's avatar - 24Qa6LT

                  Norway
                  Member #9517
                  December 10, 2004
                  1272 Posts
                  Online
                  Posted: January 12, 2014, 8:56 pm - IP Logged

                  On which dumb fooling site have you found that stuff? Puke

                  The idea of using sumproduct() is good, but the rest is bad.

                  You're probably the expert, but I'm not that picky as long as I get the correct results.

                  I found it here: http://www.mrexcel.com/forum/excel-questions/626774-count-duplicate-values-between-two-columns.html

                  The page got a few other suggestions as well.

                    SergeM's avatar - slow icon.png
                    Economy class
                    Belgium
                    Member #123700
                    February 27, 2012
                    4035 Posts
                    Offline
                    Posted: January 12, 2014, 9:06 pm - IP Logged

                    You're probably the expert, but I'm not that picky as long as I get the correct results.

                    I found it here: http://www.mrexcel.com/forum/excel-questions/626774-count-duplicate-values-between-two-columns.html

                    The page got a few other suggestions as well.

                    You posted the wrong function. You needed =SUMPRODUCT(COUNTIF($A$2:$A$10,$B2:$B10)). Use comma or semicolon according to your OS.

                    Sumproduct works, but there is no product, just a sum. Both functions aren't usable for pick 3/4.

                    You are supposed to pick the easy one.

                    I stopped using nested functions for custom functions.

                      SergeM's avatar - slow icon.png
                      Economy class
                      Belgium
                      Member #123700
                      February 27, 2012
                      4035 Posts
                      Offline
                      Posted: January 12, 2014, 9:11 pm - IP Logged
                      TableABC
                      E123
                      F456
                      G789
                      Column h.
                      E
                      Row h.
                      A
                      Found1

                      I use three parameters, but according to school you use like seven parameters. Microsoft is sleeping, nobody is moaning.

                        MillionsWanted's avatar - 24Qa6LT

                        Norway
                        Member #9517
                        December 10, 2004
                        1272 Posts
                        Online
                        Posted: January 12, 2014, 9:28 pm - IP Logged

                        I use it for Keno, not Pick 3/4.

                          SergeM's avatar - slow icon.png
                          Economy class
                          Belgium
                          Member #123700
                          February 27, 2012
                          4035 Posts
                          Offline
                          Posted: January 12, 2014, 9:40 pm - IP Logged

                          I use: =Score(A1:A4;B1:B4)
                          One function, two parameters, for lotto type, same as keno.

                            winsumloosesum's avatar - Lottery-060.jpg
                            Pennsylvania
                            United States
                            Member #2218
                            September 1, 2003
                            5387 Posts
                            Online
                            Posted: January 15, 2014, 5:32 pm - IP Logged

                            I need to count how many times i find the same numbers in two rows:

                            Row 1:

                            1

                            2

                            3

                            4

                            5

                            Row 2:

                            1

                            3

                            5

                            8

                            9

                            =(Formula) = 3

                            Thankful for any help.

                            Not sure if this is what you wanted:

                            https://app.box.com/s/ehyteozp4h2u9f1ufcc8

                              Avatar
                              NASHVILLE, TENN
                              United States
                              Member #33372
                              February 20, 2006
                              1044 Posts
                              Offline
                              Posted: January 16, 2014, 12:25 am - IP Logged

                              You're probably the expert, but I'm not that picky as long as I get the correct results.

                              I found it here: http://www.mrexcel.com/forum/excel-questions/626774-count-duplicate-values-between-two-columns.html

                              The page got a few other suggestions as well.

                              That is a good site.  I go there often.   They need a search function, tho.