ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   I am going crazy with Sumproduct (https://www.excelbanter.com/excel-programming/347302-i-am-going-crazy-sumproduct.html)

Ramthebuffs[_20_]
I am going crazy with Sumproduct
 

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
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641

Ramthebuffs[_21_]
I am going crazy with Sumproduct
 

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


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641

Tom Ogilvy
I am going crazy with Sumproduct
 
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"
wrote in message
...

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


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:
http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641

Ramthebuffs[_22_]
I am going crazy with Sumproduct
 

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.


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641

Greg Wilson
I am going crazy with Sumproduct
 
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

"Ramthebuffs" wrote:


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.


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641

Tom Ogilvy
I am going crazy with Sumproduct
 
#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"
wrote in message
...

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.


--
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:
http://www.excelforum.com/member.php...o&userid=16429
View this thread: http://www.excelforum.com/showthread...hreadid=490641


All times are GMT +1. The time now is 02:25 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com