I am going crazy with Sumproduct

Ramthebuffs · Dec 5, 2005

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 · Dec 5, 2005

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

Tom Ogilvy · Dec 5, 2005

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 · Dec 5, 2005

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.

Guest · Dec 5, 2005

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 · Dec 5, 2005

#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]...

Null cells	1	Jan 22, 2009
Need help with please..	2	Jul 23, 2012
Calculating(Processors(2)): %	1	Feb 2, 2010
Calculating(Processor(2)): %	8	Feb 3, 2010
Scrabble Value calculation for Welsh words	0	Oct 19, 2021
error passing a formula to a cell from a string	3	Dec 22, 2005
Sumproduct error	2	Mar 26, 2010
Cell values not being properly evaluated by VLOOKUP() orSUMPRODUCT()... why?	1	Jan 27, 2014

I am going crazy with Sumproduct

Ramthebuffs

Ramthebuffs

Tom Ogilvy

Ramthebuffs

Guest

Tom Ogilvy

Ask a Question

Similar Threads