Welcome Guest
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

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.

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.

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)

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.

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')"/?>

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.

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?

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

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: January 12, 2014, 8:50 pm - IP Logged
 4 2 4 2 2 4 2 Score boxed 1 2 3 1 3 2 1 Score straight 2 3 4 1 5 4 1 Score lotto

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?

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.

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.

Economy class
Belgium
Member #123700
February 27, 2012
4035 Posts
Offline
 Posted: January 12, 2014, 9:11 pm - IP Logged
 Table A B C E 1 2 3 F 4 5 6 G 7 8 9 Column h. E Row h. A Found 1

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

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.

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.

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

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.

 Page 1 of 2