SUMPRODUCT function...

Ok... I have tried to understand the SUMPRODUCT function and I think I have a pretty good understanding of it at this point. That being said, I have reached a point in creating my formula where I need help. Part of my formula requires information about the opposing team. So I am attempting to SUMPRODUCT all the times in column a - in the games sheet - when Duke appears * their opponents weighted win% (and afterwards I divide by number of games played to get an average of their opponents weighted win%) However, I am not sure how to write the second part of the equation or what function I should use AFTER - SUMPRODUCT. Here is a the line of the equation in its most basic format that is not working:

=((SUMPRODUCT(Games!A3:A142=$A3)*(VLOOKUP(Games!C3,Teams!A3:Q22,17))/($F3+$G3)))

And I have attached the speadsheet and I will highlight the column I am struggling with. If I can fix this I am DONE!!! So any help would be appreciated!!! You guys rock!!!

That helped... However, there is still a problem. Boston College played four home games, which we have accounted for, against 4 different opponents, and the formula is only registering one opponent four times (games!c3). How can I get the vlookup to follow the column down as different opponents are entered and not be stationary at games!c3? I.e. BC's second home game is against NC State, not Wake Forest... thus, I would like to take into account NC state, and other teams records that BC played. Thanks again... that was a big step forward!!!

Sure... Take a look at the speadsheet... I have highlighted the nine opponents of Boston College... (Virginia Tech counts twice because they played twice and thus I highlighted them in green.) On the Games worksheet I have highlighted the games that reference the opponents of boston college. So... mathematically what the formula needs to look like once the numbers are found is this:

.685 <wake> + .251 <nc state> + .49 <georgia tech> + .986 <virginia tech> + .514 <florida st> + .408 <maryland> + .686 <clemson> + .186 <miami> + .986 <virginia tech> / 9 <number of games played>

Hope this made more sense, and if you know of a better way of doing this other than a sumproduct with an embedded vlookup for each column that has a team name in it on the games worksheet feel free to tell me... Thanks for all your help!!!

Uggggggg... I should have known to make a new column with the vlookup to reference!!! That is it!!! Thank you so much for your help!!! I think I can get it from here. You are the man!!!