sum column of numbers based on id # and other possible criteria

[cbcool]

I am trying to do the following but cannot seem to wrap my head around it.



I have a workbook with multiple sheets. For what I am doing I have to pull information from one and compare it to another. It looks something like this:



Sheet 1

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Game #[/TD]
[TD]Cards[/TD]
[TD]Home ID[/TD]
[TD]Home Score[/TD]
[TD]Away ID[/TD]
[TD]Away Score[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]145[/TD]
[TD]2[/TD]
[TD]153[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]11[/TD]
[TD]1[/TD]
[TD]156[/TD]
[TD]3[/TD]
[TD]151[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]158[/TD]
[TD]1[/TD]
[TD]149[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]13[/TD]
[TD]1[/TD]
[TD]160[/TD]
[TD]5[/TD]
[TD]147[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]14[/TD]
[TD][/TD]
[TD]162[/TD]
[TD]4[/TD]
[TD]160[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]15[/TD]
[TD][/TD]
[TD]147[/TD]
[TD]1[/TD]
[TD]162[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]16[/TD]
[TD]1[/TD]
[TD]149[/TD]
[TD]2[/TD]
[TD]158[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]17[/TD]
[TD][/TD]
[TD]151[/TD]
[TD]0[/TD]
[TD]156[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]18[/TD]
[TD]1[/TD]
[TD]153[/TD]
[TD]3[/TD]
[TD]145[/TD]
[TD]2[/TD]
[/TR]
</tbody>[/TABLE]
Sheet 2

[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Team ID[/TD]
[TD]Team Name[/TD]
[TD]Flight[/TD]
[TD]Cards[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]145[/TD]
[TD]Red[/TD]
[TD]12b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]147[/TD]
[TD]Blue[/TD]
[TD]12b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]149[/TD]
[TD]Green[/TD]
[TD]12b[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]151[/TD]
[TD]Black[/TD]
[TD]12a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]153[/TD]
[TD]White[/TD]
[TD]12a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]156[/TD]
[TD]Orange[/TD]
[TD]12a[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]158[/TD]
[TD]Silver[/TD]
[TD]12c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]160[/TD]
[TD]Purple[/TD]
[TD]12c[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]162[/TD]
[TD]Yellow[/TD]
[TD]12c[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]












On sheet 2, I am trying to count the number of cards for each team from sheet 1, however, I only need to count cards from the winning team or the home team in the event of a tie. So I need to compare the team id with the Home ID/Away ID and if it matches see if the team won, and if they did count if there was a card present or not.



So using team 145 - they played 2 games (game # 10 & 18). In game 10, 145 was the home team and they tied. So I would look to see if there was a card and there was not so I would return a 0 in cell D2. In game 18, 145 was the away team and lost the game. So I would also return a 0 in cell D2 even though a card was present in the game.



Team 149 played 2 games (12 & 16). Game 12, 148 won the game 3-1 but there were no cards so I would return a 0 in cell D4. Game 16, 149 won 2-1 and there was a card present so I would return a 1 in cell D4.



I have thousands of games to sort through with hundreds of teams so any help I can get would really be appreciated.

 

[DanteAmor]

Try this



=SUMPRODUCT((Sheet1!$C$2:$C$10=A2)*(Sheet1!$D$2:$D$10>=Sheet1!$F$2:$F$10)*(Sheet1!$B$2:$B$10>0))+SUMPRODUCT((Sheet1!$E$2:$E$10=A2)*(Sheet1!$F$2:$F$10>=Sheet1!$D$2:$D$10)*(Sheet1!$B$2:$B$10>0))

 

[cbcool]

That worked perfectly once I changed it to not count ties for the away teams. Thank you so much!

Try this



=SUMPRODUCT((Sheet1!$C$2:$C$10=A2)*(Sheet1!$D$2:$D$10>=Sheet1!$F$2:$F$10)*(Sheet1!$B$2:$B$10>0))+SUMPRODUCT((Sheet1!$E$2:$E$10=A2)*(Sheet1!$F$2:$F$10>=Sheet1!$D$2:$D$10)*(Sheet1!$B$2:$B$10>0))

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.