| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
use of sumproduct function
I wrote this fomula in a cell in the workbook <book5
=SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 the result of the formula is 0(not #value) Does it mean this type of using the sumproduct function is not allowed or have I done anything wrong?. of course later I tentatively solved the problem by using sumproduct in book4 itself and transferring the data to Book5 by using vlookup function.because Book5 contains unique values of column A of Book4. thanks |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of sumproduct function
There is nothing that restricts sumproduct using it from one workbook to
another, of course you didn't paste in the whole formula but the only way would be some formatting issues with numbers seen as text or leading/trailing spaces If a formula wouldn't work because of limits you would get an error -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... I wrote this fomula in a cell in the workbook <book5 =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 the result of the formula is 0(not #value) Does it mean this type of using the sumproduct function is not allowed or have I done anything wrong?. of course later I tentatively solved the problem by using sumproduct in book4 itself and transferring the data to Book5 by using vlookup function.because Book5 contains unique values of column A of Book4. thanks |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of sumproduct function
thank you very much for the prompt elucidation.
the numbers are in number format. I also checked by pearsons addin for any lading or trailing spaces. I did not make it clear. sorry The point here is condition(A2) is in workbook Book5 and the data for the sumproduct is in another workbook Book4 and the formula is entered in the workbook Book5. the formula is repeated for easy refernce entry in BOOK5.sheet1.range("B2") is =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 thank you once again. XP/excel 2002 "Peo Sjoblom" wrote in message ... There is nothing that restricts sumproduct using it from one workbook to another, of course you didn't paste in the whole formula but the only way would be some formatting issues with numbers seen as text or leading/trailing spaces If a formula wouldn't work because of limits you would get an error -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... I wrote this fomula in a cell in the workbook <book5 =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 the result of the formula is 0(not #value) Does it mean this type of using the sumproduct function is not allowed or have I done anything wrong?. of course later I tentatively solved the problem by using sumproduct in book4 itself and transferring the data to Book5 by using vlookup function.because Book5 contains unique values of column A of Book4. thanks |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of sumproduct function
This works for me
=SUMPRODUCT(--([Book4]Sheet1!$A$2:$A$16=A2),[Book4]Sheet1!$B$2:$B$16) or =SUMPRODUCT(([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16) what your probably did was to leave out a parenthesis and then Excel gave you the advice to change the formula and that advice was incorrect the benefit of the former is that it ignores text in case there should be text by mistake or result from IF like a blank "" while the latter will return #VALUE! -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... thank you very much for the prompt elucidation. the numbers are in number format. I also checked by pearsons addin for any lading or trailing spaces. I did not make it clear. sorry The point here is condition(A2) is in workbook Book5 and the data for the sumproduct is in another workbook Book4 and the formula is entered in the workbook Book5. the formula is repeated for easy refernce entry in BOOK5.sheet1.range("B2") is =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 thank you once again. XP/excel 2002 "Peo Sjoblom" wrote in message ... There is nothing that restricts sumproduct using it from one workbook to another, of course you didn't paste in the whole formula but the only way would be some formatting issues with numbers seen as text or leading/trailing spaces If a formula wouldn't work because of limits you would get an error -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... I wrote this fomula in a cell in the workbook <book5 =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 the result of the formula is 0(not #value) Does it mean this type of using the sumproduct function is not allowed or have I done anything wrong?. of course later I tentatively solved the problem by using sumproduct in book4 itself and transferring the data to Book5 by using vlookup function.because Book5 contains unique values of column A of Book4. thanks |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
use of sumproduct function
thank you thank you.
"Peo Sjoblom" wrote in message ... This works for me =SUMPRODUCT(--([Book4]Sheet1!$A$2:$A$16=A2),[Book4]Sheet1!$B$2:$B$16) or =SUMPRODUCT(([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16) what your probably did was to leave out a parenthesis and then Excel gave you the advice to change the formula and that advice was incorrect the benefit of the former is that it ignores text in case there should be text by mistake or result from IF like a blank "" while the latter will return #VALUE! -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... thank you very much for the prompt elucidation. the numbers are in number format. I also checked by pearsons addin for any lading or trailing spaces. I did not make it clear. sorry The point here is condition(A2) is in workbook Book5 and the data for the sumproduct is in another workbook Book4 and the formula is entered in the workbook Book5. the formula is repeated for easy refernce entry in BOOK5.sheet1.range("B2") is =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 thank you once again. XP/excel 2002 "Peo Sjoblom" wrote in message ... There is nothing that restricts sumproduct using it from one workbook to another, of course you didn't paste in the whole formula but the only way would be some formatting issues with numbers seen as text or leading/trailing spaces If a formula wouldn't work because of limits you would get an error -- Regards, Peo Sjoblom Portland, Oregon "R..VENKATARAMAN" wrote in message ... I wrote this fomula in a cell in the workbook <book5 =SUMPRODUCT([Book4]Sheet1!$A$2:$A$16=A2)*[Book4]Sheet1!$B$2:$B$16 the result of the formula is 0(not #value) Does it mean this type of using the sumproduct function is not allowed or have I done anything wrong?. of course later I tentatively solved the problem by using sumproduct in book4 itself and transferring the data to Book5 by using vlookup function.because Book5 contains unique values of column A of Book4. thanks |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Sumproduct function | Excel Discussion (Misc queries) | |||
| clock | Excel Worksheet Functions | |||
| SUMIF function | Excel Worksheet Functions | |||
| HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions | |||
| adding two sumproduct formulas together | Excel Worksheet Functions | |||
Linear Mode
