Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif formula
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif formula
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)+S UMIF(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)+S UMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$B$2:$B$100) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Paul E" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif formula
Thanks John, but where in this formula do I determine which team to count?
Is it SUNIF "team Name" Paul "John C" wrote: 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)+S UMIF(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)+S UMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$B$2:$B$100) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Paul E" wrote: 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif formula
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 "Paul E" wrote: Thanks John, but where in this formula do I determine which team to count? Is it SUNIF "team Name" Paul "John C" wrote: 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)+S UMIF(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)+S UMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$B$2:$B$100) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Paul E" wrote: 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
countif formula
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$20Scores!$H$2:$H$20))+SUMPRODUCT(--(Scores!$G$2:$G$20=$A2),--(Scores!$H$2:$H$20Scores!$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)+SU MIF(Scores!$G$2:$G$20,$A2,Scores!$H$2:$H$20) F2: =SUMIF(Scores!$E$2:$E$20,$A2,Scores!$H$2:$H$20)+SU MIF(Scores!$G$2:$G$20,$A2,Scores!$F$2:$F$20) -- ** John C ** "Paul E" wrote: 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 "Paul E" wrote: Thanks John, but where in this formula do I determine which team to count? Is it SUNIF "team Name" Paul "John C" wrote: 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)+S UMIF(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)+S UMIF(Sheet1!$C$2:$C$100,A2,Sheet1!$B$2:$B$100) -- ** John C ** Please remember if your question is answered, to mark it answered :). It helps everyone. "Paul E" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula Help (countif, I think....) | Excel Discussion (Misc queries) | |||
Countif formula | Excel Discussion (Misc queries) | |||
help in countif formula. | Excel Discussion (Misc queries) | |||
CountIf formula results in the formula itself being displayed. | Excel Worksheet Functions | |||
CountIf formula help | Excel Discussion (Misc queries) |