I am going crazy with Sumproduct

Programming and Web Development Forums - MS EXCEL - Post any VBA, Macros, Add-Ins,Worksheet Functions; Excel Charting questions here

Skip to content

I am going crazy with Sumproduct

MS EXCEL
Post a reply

    Next

  • 1. Automated userform inputs to the spreadsheet
    I am using a userform to input 17 different pieces of data into 17 textboxes/comboboxes. When the data has been input, and I presses a button, I want it to place this data into cells on my spreadsheet. The first piece of data in cell "B2", the second into "C2" and so on down the row. Each of the input boxes has a different name which I gave it to make it easier for me to keep track of, like "username" instead of "combobox1", etc. Is there an automated way of having it input the data into the correct cells?
  • 2. lost ability to see worksheets
    All A critical workbook stopped showing its three worksheets...I just got the workbook without any sheets showing. Amazingly, I could view the sheets with Web Page Review. My solution wasn't pretty. I used Object Rexx's Office Automation capablity bring up the offending workbook and then set each sheet as current. I then used Copy/Move Sheet to export each sheet to a new workbook. Excel 2002, Win2K Q1: Is the loss of seeing worksheets a know problem? Q2: Can this type of problem be solved without going into VBA or Office Automation? REX
  • 3. Macro that runs entered value through "low" and "high" range
    Is there a way to write a macro that can take the entered value from th cells A2-A10 and run it through the "low" and "high" range an automatically enter the correct codes to cells B2-B10 in the dat sheet. code low high 1 0 5 2 6 10 3 11 15 4 16 20 5 21 25 6 16 30 7 31 35 8 36 40 9 41 45 10 46 50 11 51 55 For Example: A B 7 2 3 1 53 11 42 9 32 7 53 11 14 3 28 6 5 1 Thank you very much for any hel

I am going crazy with Sumproduct

Postby Ramthebuffs » Tue, 06 Dec 2005 11:42:04 GMT

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) = ("=S
Top

Re: I am going crazy with Sumproduct

Postby Ramthebuffs » Tue, 06 Dec 2005 11:47:56 GMT 

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


-- 
Ramthebuffs
------------------------------------------------------------------------
Ramthebuffs's Profile:  http://www.**--****.com/ 
View this thread:  http://www.**--****.com/
Top

Re: I am going crazy with Sumproduct

Postby Tom Ogilvy » Tue, 06 Dec 2005 12:47:59 GMT 

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" < XXXX@XXXXX.COM >




 http://www.**--****.com/
Top

Re: I am going crazy with Sumproduct

Postby Ramthebuffs » Tue, 06 Dec 2005 13:05:42 GMT 

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.**--****.com/ 
View this thread:  http://www.**--****.com/
Top

Re: I am going crazy with Sumproduct

Postby R3JlZyBXaWxzb24 » Tue, 06 Dec 2005 13:42:02 GMT 

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
Top

Re: I am going crazy with Sumproduct

Postby Tom Ogilvy » Tue, 06 Dec 2005 22:13:40 GMT 

#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" < XXXX@XXXXX.COM >




 http://www.**--****.com/
Top

Similar Threads:

1.Help I am going crazy I am trying to merge a label list excel

2.Help I am going crazy I am trying to merge a label list excel to w 

I do all the things that help said to and it will not go in, nothing shows up 
on the labels I have a list set up in excel and I can not get it into word as 
mailing labels it is driving me crazy. I tried it as a worksheet and as a 
list. 
I open excel and enter it, as follows
Frodo frank 2541 west St. Hobbit pa 19130 
I saved it as both a work sheet and tried it as a list, as well at a 
different time. and tried both ways.
I have 600 names and adresses on my full list.
I need an exact step by step way to merge from excel 2003 into word 2003 
labels avery label # 5160 thats a 30 label page. I have tried to do it the 
way help says to do it and the labels come up blank and it does not help. 
Please do not refer me to help pages, I need a step by step directions. As in 
save it as ------ data base and so on. any help will be tremendously 
appreciated. I tried all their help pages no good.
Archer

3.Help I am going crazy I am trying to merge a label list excel

4.Help I am going crazy I am trying to merge a label list excel to w 

I do all the things that help said to and it will not go in, nothing shows up 
on the labels I have a list set up in excel and I can not get it into word as 
mailing labels it is driving me crazy. I tried it as a worksheet and as a 
list. 
I open excel and enter it, as follows
Frodo frank 2541 west St. Hobbit pa 19130 
I saved it as both a work sheet and tried it as a list, as well at a 
different time. and tried both ways.
I have 600 names and adresses on my full list.
I need an exact step by step way to merge from excel 2003 into word 2003 
labels avery label # 5160 thats a 30 label page. I have tried to do it the 
way help says to do it and the labels come up blank and it does not help. 
Please do not refer me to help pages, I need a step by step directions. As in 
save it as ------ data base and so on. any help will be tremendously 
appreciated. I tried all their help pages no good.
Archer

5.I need help with a formula PLEASE I am going crazy 

I am creating a tracker for my work time and I am getting fusterated at 
creating this formula, any help would be apreciated:

PTO=P
Unpaid=U
Tardy Occurrence=T
Exhausted all PTO=EP
Specialized PTO=SP
Bereavement=B
Unscheduled PTO=UP
PTO Manager Override=MO
Floating Holiday=MO
Jury Duty=JD
FMLA=FM
NML=NM

These are the words I am working with, I need it to work like this:

if B2="UPAID" (is choosen from the list)THEN RETURN "U"in B3, IF B2="PTO", 
THEN RETURN "P" in B3 .....ECT...   This has to work all

6. Help I am going crazy with this formula.

7. CF goes crazy when deleting/inserting rows

8. Excel 2003 has gone crazy!!

Post a reply

Return to MS EXCEL

 

Who is online

Users browsing this forum: No registered users and 71 guest

Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group