SUMPRODUCT/COUNTIFS multuiple criteria into a multi dimensional array

[jacko2401]

Hi

I have a worksheet called Fixtures that includes basic fixture details of matches (columns A to E), the result (Column F) and players that played in that match (Columns H, J, L, N...etc) including if they scored (Columns I, K, M, O...etc).

The Result Column F contains Win, Lose, Draw.
The player columns H, J, L, N contain player names
The goals scored columns I, K, M, O contain integer numbers of goals scored

I have another worksheet called stats that holds different stats on appearances and goals. I use a multi dimensional array to complete the stats table that works well for all stats I have so far. I would like to put an extra column in the array that shows win rate (show the % of games that the player played where there was a win). I have tried Application.WorksheetFunction.Sumproduct but I cannot seem to get them to work.

The code I have for Sumproduct is:

Dim gameresult as Range, allapps as Range, players As Range
Dim i As Integer

gameresult = Sheets("Fixtures").Range("F2:F20")
allapps = Sheets("Fixtures").Range("H2:O20")

Dim PlayersList(1 to 20, 1 to 5)

i = 1

For i = 1 to 20

PlayersList(i, 5) = Application.WorksheetFunction.SumProduct(--(gameresult = "Win"), --(allapps = player.Value))

i = i + 1
Next i


However, I get a runtime error 13: Type Mismatch. Can anyone tell me where I am going wrong with SumProduct or whether I can use Countifs with teh data types and the ranges I define in VB.

 

[jacko2401]

HiI've done a bit of digging and found that EVALUATE could allow me to put the resulting value into a cell (of which I think I should be able to put into a variable or array instead). When I've modified the relevant section of code to put the resulting value to a variable, the cell value changes to #NAME! when using SUMPRODUCT. Here is the code I have:Dim gameresult as Range 'This is the range of cells where the result of the match is inputtedDim allapps as Range 'This is the range of cells that have the players that played in each game Dim PlayerList As Range ' This is teh range of cells that have the registered player listDim Winrate() As Array 'This is the array I want to put the result of the sumproduct intoDim i As Integer 'Used to go through each playerSet gameresult = Sheets("Fixtures").Range("F2:F20")Set allapps = Sheets("Fixtures").Range("H2:AU20")Set PlayerList = Sheets("Players").Range("A1:A50")Redim Winrate(1 To PlayerList.Cells.Count)i = 1For each cell in PlayerList Winrate("A" & i) = Evaluate("=SumProduct((allapps=cell.value) * (gameresult=""Win""))") i = i + 1Next The code basically loops through each player name in the Player list worksheet and places the sumproduct result into the array Winrate(). The Sumproduct checks the allapps range to see if the player name exists as well as if the result on each row is a Win. The result is the number of times that player played a game and there was a win.As I am new to Evaluate and this situation, I need to know if I'm getting the basics rights and whether my syntax is correct? Also, is there a better way to do this?