|
SUMPRODUCT
I am trying to re-create a spreadsheet into a single file where previously I
was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Hi Mark
What do you have on Sheet Data in cell A3 and A19? Are they numbers or text? Are the numbers true numbers or Text numbers compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Hi Mark
Try it as =SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19)) Does that make any difference? -- Regards Roger Govier "Mark Allen" wrote in message ... Roger please see answers below: "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? **A3 and A19 are on the calculation sheet S Wright. This is where I start the equation from Are they numbers or text? Are the numbers true numbers or Text numbers ** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900 compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. ** NO -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Roger,
this is something to do with the cell format for Data on L$1:L$10000 if I go to one that is just a number edit and press return it then calculates !!! "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? Are they numbers or text? Are the numbers true numbers or Text numbers compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
No still the same !!
"Roger Govier" wrote: Hi Mark Try it as =SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19)) Does that make any difference? -- Regards Roger Govier "Mark Allen" wrote in message ... Roger please see answers below: "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? **A3 and A19 are on the calculation sheet S Wright. This is where I start the equation from Are they numbers or text? Are the numbers true numbers or Text numbers ** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900 compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. ** NO -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Hi Mark
When you copied the data from the other workbook, did you Paste or Paste SpecialValues? If there are formulae sat in column L and they haven't been evaluated, then may this workbook has its calculation mode set to Manual. ToolsOptionsCalculationAutomatic -- Regards Roger Govier "Mark Allen" wrote in message ... Roger, this is something to do with the cell format for Data on L$1:L$10000 if I go to one that is just a number edit and press return it then calculates !!! "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? Are they numbers or text? Are the numbers true numbers or Text numbers compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Hi Roger, are you still there ??
Have you any more suggestions ?? Mark "Roger Govier" wrote: Hi Mark Try it as =SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19)) Does that make any difference? -- Regards Roger Govier "Mark Allen" wrote in message ... Roger please see answers below: "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? **A3 and A19 are on the calculation sheet S Wright. This is where I start the equation from Are they numbers or text? Are the numbers true numbers or Text numbers ** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900 compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. ** NO -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
see response to your 16:29 post about column L
-- Regards Roger Govier "Mark Allen" wrote in message ... Hi Roger, are you still there ?? Have you any more suggestions ?? Mark "Roger Govier" wrote: Hi Mark Try it as =SUMPRODUCT(--(Data!$C$1:$C$10000=$A$3),--(Data!$L$1:$L$10000=A19)) Does that make any difference? -- Regards Roger Govier "Mark Allen" wrote in message ... Roger please see answers below: "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? **A3 and A19 are on the calculation sheet S Wright. This is where I start the equation from Are they numbers or text? Are the numbers true numbers or Text numbers ** There is both numbers and text. ie. A3=Suzanne Wright and A19=CPS900 compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. ** NO -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
Roger,
the data comes from an SQL database straight into a new sheet.. how frustrating.....I am going to throw the lap top out of the window !!!! Mark "Roger Govier" wrote: Hi Mark When you copied the data from the other workbook, did you Paste or Paste SpecialValues? If there are formulae sat in column L and they haven't been evaluated, then may this workbook has its calculation mode set to Manual. ToolsOptionsCalculationAutomatic -- Regards Roger Govier "Mark Allen" wrote in message ... Roger, this is something to do with the cell format for Data on L$1:L$10000 if I go to one that is just a number edit and press return it then calculates !!! "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? Are they numbers or text? Are the numbers true numbers or Text numbers compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
SUMPRODUCT
What does the data look like - text, numbers, both? What is in A3 and A19?
Since you can edit a cell, press enter, and get it to work, it sounds like the data is supposed to be numeric, but is coming in as text. Try copying a blank cell, then select your range of data (that I assume is supposed to be numeric) and click edit/paste special/Add. Does that do anything? "Mark Allen" wrote: Roger, the data comes from an SQL database straight into a new sheet.. how frustrating.....I am going to throw the lap top out of the window !!!! Mark "Roger Govier" wrote: Hi Mark When you copied the data from the other workbook, did you Paste or Paste SpecialValues? If there are formulae sat in column L and they haven't been evaluated, then may this workbook has its calculation mode set to Manual. ToolsOptionsCalculationAutomatic -- Regards Roger Govier "Mark Allen" wrote in message ... Roger, this is something to do with the cell format for Data on L$1:L$10000 if I go to one that is just a number edit and press return it then calculates !!! "Roger Govier" wrote: Hi Mark What do you have on Sheet Data in cell A3 and A19? Are they numbers or text? Are the numbers true numbers or Text numbers compared with the data in the relevant columns. Does the Text have any leading or trailing spaces. -- Regards Roger Govier "Mark Allen" <Mark wrote in message ... I am trying to re-create a spreadsheet into a single file where previously I was linking to another but I am now having problems. Original code: =SUMPRODUCT(('No Prospects.xls'!$D$1:$D$10000=$A$3)*('No Prospects.xls'!$G$1:$G$10000=A19)) Returns a value of 2 New code: =SUMPRODUCT((Data!$C$1:$C$10000=$A$3)*(Data!$L$1:$ L$10000=A19)) Returns a value of 0 !!!!! should be 2 I know the cell range has changed i.e. D to C and G to L but this should not matter. Please help as this is really frustrating !!!! Mark |
| All times are GMT +1. The time now is 10:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com