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
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" <[email protected]> wrote in message
news:[email protected]...
>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
>
>
>
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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>>
>>
>
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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>>
>>>
>>>
>>
>
>
thank you thank you.
"Peo Sjoblom" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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" <[email protected]> wrote in message
>>> news:[email protected]...
>>>>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
>>>>
>>>>
>>>>
>>>
>>
>>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks