If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#11
|
|||
|
|||
Sumproduct help
Thanks to Roger and Bob.
I didn't realize those errors on the Weekly Meter Totals sheet would affect the other sheet as I was only dealing with sample data for the first couple of weeks in 2007. I would have never thought to check where you did. When the 2006 sheets were made, I made the sheets (in systematic order) and everything worked. Thanks to both you I have learned that the computer thinks differently from me. :-) Thanks again Walter Mayes "Bob Phillips" wrote in message ... The problem is on Weekly Meter Totals. there are many #REF errors there that need to be resolved. The formulae that aren't #REF there seem to be pointing at the wrong cells (H5, I5, etc), should be $A5 etc. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Walter Mayes" wrote in message ... Tried that but no luck. I'll send you the books. I have also sent the books to Bob. Thanks Walter Mayes "Roger Govier" wrote in message ... Hi Walter You are misunderstanding what I said. I agree that your number of parentheses are correct in total. Yes you do have 2 at the end. But it should be =SUMPRODUCT( ) with ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* ('wkly meter totals'!$c$4:$bb$41) between the sumproduct parentheses What you have is =SUMPRODUCT(( ) with ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* 'wkly meter totals'!$c$4:$bb$41) If that amendment doesn't sort it out for you, feel free to email me the workbook. Remove NOSPAM from my email address to mail direct -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Walter It looks to me as though you have a parenthesis in the wrong place Your formula starts with 3 opening parentheses, and only has a one parenthesis for the final term 'wkly meter totals'!$c$4:$bb$41) Try amending to =sumproduct( ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* ('wkly meter totals'!$c$4:$bb$41)) -- Regards Roger Govier "Walter Mayes" wrote in message ... Am converting my workbooks over from 2006 to 2007. Hope I can explain this clearly. Saved my 2006 workbook as 2007 so it should be a simple matter to delete certain 2006 data and change the dates. The below Sumproduct formula works in the 2006 sheet but I am getting a #REF error in the 2007 sheet. In the (Ticket Mach.) sheet I have the following formula: =sumproduct((('wkly meter totals'!$a$4:$a$41=a6)*('wkly meter totals'!$c$4:$bb$4=j6)*'wkly meter totals'!$c$4:$bb$41)) This works in the 2006 sheet and my answer is $57.00 This formula is dragged down and works for the whole year for each game. This same formula in the 2007 sheet gives me a #REF error. When I click on the error arrow it says "Invalid Cell Ref. error." When I click "Edit in formula bar", a6 is highlighted. Formula in Ticket Mach. sheet ( both years) is in cell k6 Types of data in both years is the same. Wkly Meter Totals: a4:a41 is text (names of games) Ticket Mach. a6 is names of games Wkly Meter Totals c4:bb4 are dates (every Monday) Ticket Mach. j6 is a date Wkly Meter Totals c4:bb41 are numbers What it should do: Look down Column A in "Wkly Meter Totals" and find the same name that is in "Ticket Mach." a6, then look across row c4:bb4 in "Wkly Meter Totals" to find the same date that is in "Ticket Mach." j6, then return the number that is found in "Wkly Meter Totals" c4:bb41 The names and numbers that are in the "Wkly Meter Totals" sheet are being picked up from a previous sheet and there are no problems with that I have copied and pasted the names to assure of correct spelling.. I have checked the formatting, (general) and (dates) and cannot locate what the problem may be. Any assistance would be appreciated. Would be willing to send both books, should anyone request them. Thanks Walter Mayes |
#12
|
|||
|
|||
Sumproduct help
Hi Walter
You're very welcome. Thanks for the feedback and a very Happy New Year. -- Regards Roger Govier "Walter Mayes" wrote in message ... Thanks to Roger and Bob. I didn't realize those errors on the Weekly Meter Totals sheet would affect the other sheet as I was only dealing with sample data for the first couple of weeks in 2007. I would have never thought to check where you did. When the 2006 sheets were made, I made the sheets (in systematic order) and everything worked. Thanks to both you I have learned that the computer thinks differently from me. :-) Thanks again Walter Mayes "Bob Phillips" wrote in message ... The problem is on Weekly Meter Totals. there are many #REF errors there that need to be resolved. The formulae that aren't #REF there seem to be pointing at the wrong cells (H5, I5, etc), should be $A5 etc. -- --- HTH Bob (change the xxxx to gmail if mailing direct) "Walter Mayes" wrote in message ... Tried that but no luck. I'll send you the books. I have also sent the books to Bob. Thanks Walter Mayes "Roger Govier" wrote in message ... Hi Walter You are misunderstanding what I said. I agree that your number of parentheses are correct in total. Yes you do have 2 at the end. But it should be =SUMPRODUCT( ) with ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* ('wkly meter totals'!$c$4:$bb$41) between the sumproduct parentheses What you have is =SUMPRODUCT(( ) with ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* 'wkly meter totals'!$c$4:$bb$41) If that amendment doesn't sort it out for you, feel free to email me the workbook. Remove NOSPAM from my email address to mail direct -- Regards Roger Govier "Roger Govier" wrote in message ... Hi Walter It looks to me as though you have a parenthesis in the wrong place Your formula starts with 3 opening parentheses, and only has a one parenthesis for the final term 'wkly meter totals'!$c$4:$bb$41) Try amending to =sumproduct( ('wkly meter totals'!$a$4:$a$41=a6)* ('wkly meter totals'!$c$4:$bb$4=j6)* ('wkly meter totals'!$c$4:$bb$41)) -- Regards Roger Govier "Walter Mayes" wrote in message ... Am converting my workbooks over from 2006 to 2007. Hope I can explain this clearly. Saved my 2006 workbook as 2007 so it should be a simple matter to delete certain 2006 data and change the dates. The below Sumproduct formula works in the 2006 sheet but I am getting a #REF error in the 2007 sheet. In the (Ticket Mach.) sheet I have the following formula: =sumproduct((('wkly meter totals'!$a$4:$a$41=a6)*('wkly meter totals'!$c$4:$bb$4=j6)*'wkly meter totals'!$c$4:$bb$41)) This works in the 2006 sheet and my answer is $57.00 This formula is dragged down and works for the whole year for each game. This same formula in the 2007 sheet gives me a #REF error. When I click on the error arrow it says "Invalid Cell Ref. error." When I click "Edit in formula bar", a6 is highlighted. Formula in Ticket Mach. sheet ( both years) is in cell k6 Types of data in both years is the same. Wkly Meter Totals: a4:a41 is text (names of games) Ticket Mach. a6 is names of games Wkly Meter Totals c4:bb4 are dates (every Monday) Ticket Mach. j6 is a date Wkly Meter Totals c4:bb41 are numbers What it should do: Look down Column A in "Wkly Meter Totals" and find the same name that is in "Ticket Mach." a6, then look across row c4:bb4 in "Wkly Meter Totals" to find the same date that is in "Ticket Mach." j6, then return the number that is found in "Wkly Meter Totals" c4:bb41 The names and numbers that are in the "Wkly Meter Totals" sheet are being picked up from a previous sheet and there are no problems with that I have copied and pasted the names to assure of correct spelling.. I have checked the formatting, (general) and (dates) and cannot locate what the problem may be. Any assistance would be appreciated. Would be willing to send both books, should anyone request them. Thanks Walter Mayes |
|
Thread Tools | |
Display Modes | |
|
|