Confusing formula using Sumproduct/SumIF

[brooksc29]

I am trying to write a formula that will calculate the following:

[ For all of the cells in col AJ on Data worksheet that have a value > 0 where col A in the row matches the player name in A5 between the dates on worksheet TEAM SHEET B1:B2 ],

[ sum the values from D2:D where the row matches the player name in A5 between the dates on worksheet TEAM SHEET B1:B2 ]
and

[ divide that by the sum of the values from C2:C where the row matches the player name in A5 between the dates on worksheet TEAM SHEET B1:B2]

so right now I am using this =IFERROR(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02)), "-") to calculate the total number of instances in col AJ on Data worksheet for the name in A5.

I just need to combine each instance of that formula with summing all the cells in col D and dividing it by the sum of the cells in col C where it matches the name and date range...

I don't know if this will make sense without showing the workbook, but THANK YOU so much for the help!

 

[brooksc29]

I'm starting with this and hopefully working on the right track...


=IF(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02))>=1, SUMIF(Data!$A$2:A,$A5, Data!$D$2:D))

 

[brooksc29]

okay, i think this gave me the sum of COL D, now I need to divide by the sum in col C....

=IF(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02))>=1, SUMIF(PlayerName,$A5,Data!$D$2:D193))

just not sure if it's accurate w/o listing in the date range/player name in the SUMIF part

 

[brooksc29]

OK, this seems to work!

=IF(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02))>=1,(SUMIF(PlayerName,$A5,Data!$D$2:D2000)/(SUMIF(PlayerName,$A5,Data!$C$2:C2000))))


any ideas on how to not have FALSE show up in the cell if there is no data?

 

[T. Valko]

OK, this seems to work!

=IF(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02))>=1,(SUMIF(PlayerName,$A5,Data!$D$2:D2000)/(SUMIF(PlayerName,$A5,Data!$C$2:C2000))))


any ideas on how to not have FALSE show up in the cell if there is no data?

What result do want when that happens?

Try it like this...

=IF(SUMPRODUCT((PlayerName=$A5)*(GameDate>='TEAM SHEET'!$B$1)*(GameDate<='TEAM SHEET'!$B$2)*(COUNT02)),SUMIF(PlayerName,$A5,Data!$D$2:D2000)/SUMIF(PlayerName,$A5,Data!$C$2:C2000),"")

That will leave the cell blank. If you want a result of 0 then replace this "" with a 0.