Formula
 

Hi,

I have a golf league and I am trying to create a worksheet and write a
formula that will tell me how many times golfer Jim plays with every other
golfer through his 5 rounds. Also how many times golfer Sam plays with every
other golfer and so on.

We have 12 players so each row is a foursome, and there are 3 foursomes per
day. We are playing 5 rounds.

I am trying to do the calc in columns G thru R and rows 2 thru 14

My sheet is as follows:
A B C D E F G H I
J K L
1 Jim Sam Bob Tom Jim Sam Bob Tom Dave
etc.....
2 Dave Mike Tony Alan Jim x 1 1 1
0
3 Ron Darin Joel Scott Sam 1 x 2 1
0
4 Bob 1 1 x
1 1
5 Sam Tony Scott Mike etc...
6 Alan Jim Ron Darin
7 Bob Tom Dave Joel
8
9
..
..
..
etc

Thanks for your help,
Mike

Formula
 

Not sure what you are dealing with there, but try this:
Jim in Column A; other names below.
Ron in Column B; other names below.

D1 = Jim
E1 = Ron
F1 =SUMPRODUCT((A1:A18=D1)*(B1:B18=E1))

Another possible solution:
Names in Column A and Column B; some matches and some non-matches.
F1 =IF(NOT(ISERROR(MATCH(A1:A6,B1:B6,0))),A1:A6,"")
Commit this with Ctrl+Shift+Enter (not just Enter). Fill down...

Or...
F1 =IF(ISERROR(MATCH(A1:A6,B1:B6,0)),A1:A6,"")
Ctrl+Shift+Enter...gives the opposite result.

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Michaelt721" wrote:

Hi,

I have a golf league and I am trying to create a worksheet and write a
formula that will tell me how many times golfer Jim plays with every other
golfer through his 5 rounds. Also how many times golfer Sam plays with every
other golfer and so on.

We have 12 players so each row is a foursome, and there are 3 foursomes per
day. We are playing 5 rounds.

I am trying to do the calc in columns G thru R and rows 2 thru 14

My sheet is as follows:
A B C D E F G H I
J K L
1 Jim Sam Bob Tom Jim Sam Bob Tom Dave
etc.....
2 Dave Mike Tony Alan Jim x 1 1 1
0
3 Ron Darin Joel Scott Sam 1 x 2 1
0
4 Bob 1 1 x
1 1
5 Sam Tony Scott Mike etc...
6 Alan Jim Ron Darin
7 Bob Tom Dave Joel
8
9
.
.
.
etc

Thanks for your help,
Mike