I am going crazy with Sumproduct

[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

 

[Ramthebuffs]

Wow that is ugly, not sure how to fix it to make it easier to read.

 

[Tom Ogilvy]

the code as written worked fine for me in xl97.

I don't see much point in it as for each column, it puts in an identical
formula in rows 3 to 336.

--
Regards,
Tom Ogilvy


"Ramthebuffs" <[email protected]>
wrote in message
news:[email protected]...

 

[Ramthebuffs]

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.

 

[Greg Wilson]

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

 

[Tom Ogilvy]

#N/A would usually indicate that you have a #N/A error in your source data -
not that there is a problem with the formula.

--
Regards,
Tom Ogilvy


"Ramthebuffs" <[email protected]>
wrote in message
news:[email protected]...