Unknown Formula?

Hey drc,

I made a couple of tables to model what you were talking about. The first table consists of the first four columns below. Player is the player in the pool. The table is saying that in week 1, player 1 selected teams 4 and 1 to win and player 2 selected teams 2 and 4 to win. The second table is the results table. It says that in week 1 teams 1 and 3 won their games and in week 2 teams 2 and 3 won their games. (Note: There's a weakness here in that the games could possibly be tied. This can be handled, too, with a bit more work.) Keep reading below.

Player Week Team Result Week Team
1 1 4 0 1 1
1 1 1 1 1 3
2 1 2 0 2 2
2 1 4 0 2 3
3 1 1 1
3 1 3 1
1 2 4 0
1 2 1 0
2 2 2 1
2 2 4 0
3 2 1 0
3 2 3 1

The result column contains an Array formula. It uses named ranges based on the Results table. Team is =Sheet3!$G$2:$G$5 and Week is =Sheet3!$F$2:$F$5.

Then the Array formula in cell D2 (right beneath the header Result) is =SUM((B2=Week)*(C2=Team)).

NOTE: when you enter this fomula you MUST finish pressing <Ctrl><Shift><Enter> all at the same time. Then you can just copy this formula to the cells beneath it.

To get totals for each player in the pool I'd recommend using a pivot table.

I need to learn how to post columnar data better. Table 1 consists of the first four columns and Table 2 consists of the fifth and sixth columns.

Hey drc,

I used Named Ranges to make the formulas look a bit cleaner but you may not be used to them. If you didn't create them properly, that might be the cause of ?NAME error.

You shouldn't have 0's in your Week or Team columns.

The tables can also be laid out like this:

Player Week Team Result
1 1 4 0
1 1 1 1
2 1 2 0
2 1 4 0
3 1 1 1
3 1 3 1
1 2 4 0
1 2 1 0
2 2 2 1
2 2 4 0
3 2 1 0
3 2 3 1

Week Team
1 1
1 3
2 2
2 3

Only the Result column has 0's in it.

But I think you also need to learn about Array formulas. Mr. Excel has some very good explanations. I find one or two and post links.

I got to the Pivot Table and Dont understand where im going with that.....

These are Array formulas not pivot tables.

Here are a couple of array formula links on this website:
http://www.mrexcel.com/tip011.shtml
http://www.mrexcel.com/tip031.shtml

These can both be found on this page in the Formulas section near the top.
http://www.mrexcel.com/articles.shtml

I know that, i made the table then you said to get results, use a pivot table