countif formula

[Paul E]

I have a spreadsheet for my daughter team where I show the teams in different
columns as well as their scores
Home Team, Score, Visiting Team, Score

I want to automatically calculate the Goals For and Goals Against using
thios spreadsheet. Is there a standard formula that would do this for me.

I want to calculate for ALL teams, not just hers.

 

[John C]

Assumed data on sheet1
Column A=home team, column B=home team score, column C=visiting team, column
D=visiting team score. Row 1 is headers, and data starts in row 2.
On a separate sheet, to calculate goals for, and assuming all the teams are
listed starting in cell A2, would be as follows:
=SUMIF(Sheet1!$A$2:$A$100,A2,Sheet1!$B$2:$B$100)+SUMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$D$2:$D$100)
Goals against would be slightly different, but same model:
=SUMIF(Sheet1!$A$2:$A$100,A2,Sheet1!$D$2:$D$100)+SUMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$B$2:$B$100)

 

[Paul E]

Thanks John, but where in this formula do I determine which team to count?

Is it SUNIF "team Name"

Paul

 

[Paul E]

SAMPLE DATA BELOW

Hope this helps better explain.


A B C D E
F G H
Date Day Time Game # Home Score Visitor Score
09/27/08 Sat TBA 241 Jordan Peck 1 Jordan Armes 6
09/27/08 Sat TBA 261 Jordan Armes Jordan Peck
10/04/08 Sat 5:30 PM 230 Mississauga Ajax
10/05/08 Sat 8:00 PM 231 Jordan Armes 8 Ajax 4
10/05/08 Sun 4:15 PM 232 Oshawa 4 Richmond Hill 2
10/05/08 Sun 5:15 PM 233 Mississauga 3 Whitby 4
10/15/08 Wed 8:15 PM 234 Whitby 2 Ajax 3
10/18/08 Sat 4:15 PM 235 Oshawa 5 Whitby 5
10/18/08 Sat 5:15 PM 236 Mississauga 2 Richmond Hill 3
10/18/08 Sat 8:00 PM 237 Jordan Peck 5 Mississauga 4
10/18/08 Wed 7:30 PM 248 Ajax 2 Whitby 3

Paul

 

[John C]

Okay, let us assume that the sample data that you have given is on a tab, and
instead of calling it sheet 1 or sheet 2, we'll call it Scores. If I read
your data correctly, Column E has the home team name, Column F has the home
team score, Column G has the visiting team name, and Column H has the
visiting team score.
There is nothing extra that needs to be done here, this one is fine as is.
Now, on a second tab, and for the sake of argument, we'll call it standing,
we have to set it up as follows:
Column A is a list of all the teams, Column B will be Wins, Column C will be
losses, Column D is ties, Column E is Goals For, and Column F is Goals
Against. You can do more, like have winning percentage, games back, etc, but
I'll leave that to you.
Type the formulas into the Standings tab in the cells listed, then copy down
as needed for the teams.

B2:
=SUMPRODUCT(--(Scores!$E$2:$E$20=$A2),--(Scores!$F$2:$F$20>Scores!$H$2:$H$20))+SUMPRODUCT(--(Scores!$G$2:$G$20=$A2),--(Scores!$H$2:$H$20>Scores!$F$2:$F$20))

C2:
=SUMPRODUCT(--(Scores!$E$2:$E$20=$A2),--(Scores!$F$2:$F$20<Scores!$H$2:$H$20))+SUMPRODUCT(--(Scores!$G$2:$G$20=$A2),--(Scores!$H$2:$H$20<Scores!$F$2:$F$20))

D2:
=SUMPRODUCT(--(Scores!$E$2:$E$20=$A2),--(Scores!$F$2:$F$20=Scores!$H$2:$H$20),--(Scores!$F$2:$F$20<>""))+SUMPRODUCT(--(Scores!$G$2:$G$20=$A2),--(Scores!$H$2:$H$20=Scores!$F$2:$F$20),--(Scores!$F$2:$F$20<>""))

E2:
=SUMIF(Scores!$E$2:$E$20,$A2,Scores!$F$2:$F$20)+SUMIF(Scores!$G$2:$G$20,$A2,Scores!$H$2:$H$20)

F2:
=SUMIF(Scores!$E$2:$E$20,$A2,Scores!$H$2:$H$20)+SUMIF(Scores!$G$2:$G$20,$A2,Scores!$F$2:$F$20)