I am going crazy with Sumproduct

  • Thread starter Thread starter Ramthebuffs
  • Start date Start date
R

Ramthebuffs

I have code that I will put here, it runs sumproduct but its not workin
as it should. I'm thinking there must be something in settings o
something that needs to fixed. This code works flawlessly on anothe
worksheet in the exact same form except the loop goes through 3
instead of 340. The error in the code starts on the line that say
Range("W" & i). The first sumproduct in this line works, but th
second sumproduct doesn't. I'm 90% sure theres a problem in the par
after the comma that tells it to count because I can change colum
reference to a lower letter and it works.

When I do the step by step error analysis it shows the first sumproduc
as correct, but the second one says sumproduct(--(false, false,fals
about 20 times),DataBase!$X$3:$X$20000)

I really have no clue whats going on. Maybe theres a way to get "into
excel to view the page formats on a deeper level than simply righ
clicking? This error is the same on every line after the Range("W
line. The second sumproduct doesn't work.

Heres the code. Like I said it works perfectly on a differen
workbook. I tried to copy and paste special the data to a new workboo
and had the same problem. I've also tried it on an older version of th
workbook I have on a separate computer and it doesn't work.

Sub TotalsSheet()
Dim Team As String
On Error Resume Next
ThisWorkbook.Worksheets("Totals").Select

For i = 3 To 336
Team = Range("A" & i)
Range("B" & i) = ("=COUNTIF(DataBase!$B$3:$B$20000,""" & Team
""")+COUNTIF(DataBase!$C$3:$C$20000,""" & Team & """)")
Range("C" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$D$3:$D$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$N$3:$N$20000)")
Range("D" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$E$3:$E$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$O$3:$O$20000)")
Range("E" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$F$3:$F$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$P$3:$P$20000)")
Range("F" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$G$3:$G$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$Q$3:$Q$20000)")
Range("G" & i) = Range("E" & i) / Range("F" & i)
Range("H" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$I$3:$I$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$S$3:$S$20000)")
Range("I" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$J$3:$J$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$T$3:$T$20000)")
Range("J" & i) = Range("H" & i) / Range("I" & i)
Range("K" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$L$3:$L$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$V$3:$V$20000)")
Range("L" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$M$3:$M$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$W$3:$W$20000)")
Range("M" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$N$3:$N$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$D$3:$D$20000)")
Range("N" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$O$3:$O$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$E$3:$E$20000)")
Range("O" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$P$3:$P$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$F$3:$F$20000)")
Range("P" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$Q$3:$Q$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$G$3:$G$20000)")
Range("Q" & i) = Range("O" & i) / Range("P" & i)
Range("R" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$S$3:$S$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
Team & """),DataBase!$I$3:$I$20000)")
Range("S" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team
"""),DataBase!$T$3:$T$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" &
Team & """),DataBase!$J$3:$J$20000)")
Range("T" & i) = Range("R" & i) / Range("S" & i)
Range("U" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$V$3:$V$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" &
Team & """),DataBase!$L$3:$L$20000)")
Range("V" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$W$3:$W$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000=""" &
Team & """),DataBase!$M$3:$M$20000)")
Range("W" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AR$3:$AR$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$X$3:$X$20000)")
Range("X" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AS$3:$AS$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$Y$3:$Y$20000)")
Range("Y" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AT$3:$AT$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$Z$3:$Z$20000)")
Range("Z" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AU$3:$AU$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AA$3:$AA$20000)")
Range("AA" & i) = Range("Y" & i) / Range("Z" & i)
Range("AB" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AW$3:$AW$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AC$3:$AC$20000)")
Range("AC" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AX$3:$AX$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AD$3:$AD$20000)")
Range("AD" & i) = Range("AB" & i) / Range("AC" & i)
Range("AE" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$AZ$3:$AZ$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AF$3:$AF$20000)")
Range("AF" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BA$3:$BA$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AG$3:$AG$20000)")
Range("AG" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BB$3:$BB$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AH$3:$AH$20000)")
Range("AH" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BC$3:$BC$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AI$3:$AI$20000)")
Range("AI" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BD$3:$BD$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AJ$3:$AJ$20000)")
Range("AJ" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BE$3:$BE$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AK$3:$AK$20000)")
Range("AK" & i) = Range("AI" & i) / Range("AJ" & i)
Range("AL" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BG$3:$BG$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AM$3:$AM$20000)")
Range("AM" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BH$3:$BH$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AN$3:$AN$20000)")
Range("AN" & i) = Range("AL" & i) / Range("AM" & i)
Range("AO" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BJ$3:$BJ$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AP$3:$AP$20000)")
Range("AP" & i) = ("=SUMPRODUCT(--(DataBase!$B$3:$B$20000=""" & Team &
"""),DataBase!$BK$3:$BK$20000)+SUMPRODUCT(--(DataBase!$C$3:$C$20000="""
& Team & """),DataBase!$AQ$3:$AQ$20000)")



Next


End Sub
 
Thanks for the response Tom, that is my problem. It should work, but it
doesn't. It even works on different workbook. For some reason it fails
after column W though. I get the #N/A error in each cell after column
W.
 
I suggest that you start a new post and explain in a simplified form what you
want to do. It appears that the code could be greatly simplified. I for one
don't really want to sort it all out. IMHO, I think you'd be better off with
a different approach.

Regards,
Greg
 
Back
Top