Referencing multiple specific cells in a table

[owenmac99]

Hi everyone!

I need help trying to figure out how to reference multiple cells in a table. I thought it would be simple so I'm either overthinking it or it is more complicated than I thought.

I have a table of data from NHL games as follows:

Date	Away	AwayScore	RW/OT	HomeScore	Home
Dec 01	Dallas	2	OT	3	Minnesota
Dec 01	Edmonton	3	RW	2	Vancouver
Dec 03	Dallas	1	RW	5	Winnipeg
Dec 04	Ottawa	5	RW	2	Edmonton

The table is much longer and contains many more games form various teams but this gives you the idea.

Elsewhere I want to collect that data. So far it looks like this:

	GP	HomeGP	AwayGP	RW	HomeRW	AwayRW	L10 RW	W/OTL	HomeW/OTL	AwayW/OTL
Edmonton	2	1	1	0
Dallas	2	0	2	0

The Games Played (GP) column is simply =HomeGP+AwayGP.
The HomeGP and AwayGP are the first ones that needed to reference the game data. To do so I did:

=SUM(IF(Table1[Home]="Edmonton", 1, 0))

This is what I want because it's adding 1 to HomeGP each time I enter a game in which Edmonton plays at home. I did the same for AwayGP.

Where I am having issues is the following columns. Under Regulation Wins (RW) I want it to be able to add 1 any time Edmonton plays a game and their score is higher than the opponents. So if the score of the referenced team is larger than the score of the other team, return one. What I tried was:

=SUM(IF(AND(Table1[Away]="ARI", OR(Table1[AwayScore]>Table1[HomeScore], Table1[HomeScore]>Table1[AwayScore])), 1, 0))

This obviously didn't work, it just returns 0. I can't figure out a way to do this and if anyone has ideas I would be really grateful!

[Sam Capricci]

not sure I follow but is this what you are looking for?
=IF(VLOOKUP(H2,Table1[[Away]:[AwayScore]],2,FALSE)>VLOOKUP(H2,Table1[[Away]:[HomeScore]],4,FALSE),1,0)

[owenmac99]

Yes, sorry I tried to make it as little confusing as possible.

This is definitely on the right track! It does what I want it to but it seems to only be doing it for the first entry of each team

[Sam Capricci]

could you upload another example with more expected results? I didn't lock in H2 but maybe that needs to be locked into the formula like $H2 but I don't know what you are trying to do overall and how many of the cells you are trying to populate.

[owenmac99]

I added a few more. Let me know if that is good or if you need more. The idea is that I will continue to add scores each day as they happen in real life and then the main table will update with how many wins and games played each team has. There will be 31 different teams total (as in the NHL)

[Sam Capricci]

I honestly don't understand your workbook. And I asked you to give me more info, if for example you want something in HomeRW then give me an example of where it is coming from. Same with any of the columns to the right.

But back to your workbook, it appears to me you are using array functions for what could be done easier with some simple countif or countifs statements. For example, if I'm understanding your formula in J2, {=SUM(IF(Table1[Home]=H2,1,0))} seems it is returning a count of how often Edmonton sin the home column in column F. Simply =COUNTIF($F$2:$F$13,H2) would give you the same count.
and since you have an error (#N/A) in the formula I gave you because Columbus isn't in column B (yet) this will correct that.
=IFERROR(IF(VLOOKUP(H8,Table1[[Away]:[AwayScore]],2,FALSE)>VLOOKUP(H8,Table1[[Away]:[HomeScore]],4,FALSE),1,0),"")

[owenmac99]

Sorry, I should have left the other categories like HomeRW out of this. They are not relevant for the sake of figuring my problem out.

As for the COUNTIF statement, that does simplify it, so thank you.

The error is fine, that is something I know how to deal with. The issue rather, is the fact that the VLOOKUP only goes as far as counting the first time Dallas, for example, shows up. So the formula in L4, {=IF(VLOOKUP(H4,Table1[[Away]:[AwayScore]],2,FALSE)>VLOOKUP(H4,Table1[[Away]:[HomeScore]],4,FALSE),1,0)}, returns 0 because it sees that C3 is smaller than E3. However, it doesn't look past that, down to row 11 where Calgary's score (C11) is larger than Columbus's score (E11). I want it to take all of the scenarios into account, not just the first one on the table.

[JeteMc]

Try the following:
1. For the HomeRW column: =SUMPRODUCT((Table1[Home]=H2)*(Table1[RW/OT]=L$1)*(Table1[AwayScore]< Table1[HomeScore]))
2. For the AwayRW column: =SUMPRODUCT((Table1[Away]=H2)*(Table1[RW/OT]=L$1)*(Table1[AwayScore]>Table1[HomeScore]))
3. For the RW column: =SUM(M2:N2)
Let us know if you have any questions.

[owenmac99]

Yes that is perfect! Thank you!

The only other question I have is related to the L10 column (Last 10 Games). Is there a way to do the previous function for only the last ten corresponding entries in the table? So, for example, I only want to count the amount of wins that Edmonton has in their last 10 games.

[JeteMc]

Perhaps the thread linked below will be helpful.
Let us know if you have any questions.
https://www.excelforum.com/excel-for...-the-same.html

[owenmac99]

The thread below is a same idea but formatted in a different way that doesn't work with my sheet. He doesn't use the scores of the game in his, he just marks if it's a win or not. I need it to determine if the score of the referenced team is higher than the other. I tried manipulating the formulas in that thread to work for mine but I couldn't figure it out

[JeteMc]

This proposal adds two columns to the table. The added columns may be hidden for aesthetic purposes.
The Win column is populated using: =IF([@AwayScore]>[@HomeScore],[@Away],[@Home])
The Last 5* column is populated using: =IF(SUMPRODUCT(--(B2:F$1000=[@Win]))<=5,[@Win],"")
Note that the range is set to 1000 thinking that would provide enough rows for a season's worth of data, however it may be changed if needed.
*Once enough data is added to the table change <=5 to <=10 and you may choose to change the column header also.
The L5 column (X) is populated using: =COUNTIFS(Table1[Last 5],I2)
Let us know if you have any questions.