Calculate points for and points against

[Frank@D&D Office Plus]

Date - A Winner _ B Score C Mvp _ D Loser_ E Score_ F Mvp _G
10/11/2008 Team A 16 Sarah Team E 12 Taylor
10/11/2008 Team B 33 Kira Team F 14 Darian
10/11/2008 Team C 20 Wesley Team D 13 Halle
10/12/2008 Team B 35 Katelyn Team E 22 Lacey
10/12/2008 Team C 29 Allison Team G 15 Julie
10/12/2008 Team D 25 Rachel Team F 23 Olivia
10/16/2008 Team A 34 Amanda Team G 19 Mary


in the spreedsheet above, how can I calculate points for and points against,
I am going to want those totals on another sheet were I have the standings
( I calculated the standings wins, and loses using the count if)

Thanks
Frank

 

[John C]

For Points For:
=SUMPRODUCT(($B$2:$B$20="Team A")*($C$2:$C$20))+SUMPRODUCT(($E$2:$E$20="Team
A")*($F$2:$F$20))

For Points Against:
=SUMPRODUCT(($B$2:$B$20="Team A")*($F$2:$F$20))+SUMPRODUCT(($E$2:$E$20="Team
A")*($C$2:$C$20))

 

[Frank@D&D Office Plus]

I tried to cut and past, but the formula didnt work. Thanks for trying to
help me out.
any other suggestions I should try?

Thanks
Frank

 

[John C]

Well, you need to modify the ranges to suit your needs, and if the ranges are
on a separate tab, (such as Scores), the ranges should be entered thusly:
Scores!$E$2:$E$20
the tab name with exclamation mark should precede all ranges. If there is a
space in the tab name, you need to include two apostrophes in it, such as:
'Scores 2'!$E$2:$E$20.