use of sumproduct function

[R..VENKATARAMAN]

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

 

[Peo Sjoblom]

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]

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

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

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

 

[Peo Sjoblom]

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

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

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

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

 

[R..VENKATARAMAN]

thank you thank you.

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

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

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




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