error in SUMPRODUCT
 

hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

Please confirm:
1) D5 has an account number/name which can be found in L7:L3000 of sheet
Sold List
2) K7:K3000 is a list of numbers
If not please explain what you wish to accomplish
Have you checked they are all numbers. Does =COUNT(K7:K3000) yield 2994 ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tufail" wrote in message
...
hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account
List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

If any value in K7:K3000 is text you'll get that error. For a single
condition like this you only need SUMIF, i.e.

=SUMIF('Sold List'!$L$7:$L$3000,D5,'Sold List'!$K$7:$K$3000)

This formula will ignore text.

Note: If there is text you might want to determine where it is so that you
don't get an inaccurate result

"Tufail" wrote:

hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

Any chance that Row7 is a header row that might contain Text?

That would give you a #Value! error.

Also, if you have formulas in Column K that you have set up to return nulls
( "" ), those nulls (zero length strings) are technically Text, and would
cause XL to return that error.

If you *do* have formulas in K that are returning nulls, you can try to use
the unary form of Sumproduct():

=SUMPRODUCT(--('Sold List'!$L$7:$L$3000=D5),'Sold List'!$K$7:$K$3000)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tufail" wrote in message
...
hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account
List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

On Dec 16, 11:43 am, Tufail wrote:
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!

The syntax looks okay. Did you cut-and-paste the formula into your
posting? If not, recheck the formula in Excel or cut-and-paste from
your posting back into Excel.

Assuming the syntax in the Excel formula matches the above, the #VALUE
error might indicate that one or more cells in K7:K3000 is not
numeric, or one or more cells in L7:L3000 or D5 has a #VALUE error.

error in SUMPRODUCT
 

1) Yes D5 name is in Sold List ( Japan )
2) Yes in K7:K3000 are Amounts ( Numbers )

"Bernard Liengme" wrote:

Please confirm:
1) D5 has an account number/name which can be found in L7:L3000 of sheet
Sold List
2) K7:K3000 is a list of numbers
If not please explain what you wish to accomplish
Have you checked they are all numbers. Does =COUNT(K7:K3000) yield 2994 ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tufail" wrote in message
...
hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account
List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

No, I didn't cut and paste, just I input manually into the cell
K7:K3000 I have numeric and L7:L3000 text, that's why i also don't
understand that's why this is coming out !


"joeu2004" wrote:

On Dec 16, 11:43 am, Tufail wrote:
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!

The syntax looks okay. Did you cut-and-paste the formula into your
posting? If not, recheck the formula in Excel or cut-and-paste from
your posting back into Excel.

Assuming the syntax in the Excel formula matches the above, the #VALUE
error might indicate that one or more cells in K7:K3000 is not
numeric, or one or more cells in L7:L3000 or D5 has a #VALUE error.

error in SUMPRODUCT
 

I got it, yes i had in K7:K3000 minus mark like - so I removed that and
problam is solved, thanks for all of you !


"Bernard Liengme" wrote:

Please confirm:
1) D5 has an account number/name which can be found in L7:L3000 of sheet
Sold List
2) K7:K3000 is a list of numbers
If not please explain what you wish to accomplish
Have you checked they are all numbers. Does =COUNT(K7:K3000) yield 2994 ?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tufail" wrote in message
...
hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in Account
List
hope could be understand.
thanks in advance

error in SUMPRODUCT
 

Which is why I prefer this asterisk form of Sumproduct().

It lets you know when something - anything - is not right.

The unary by-passes these "contaminated" inputs.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Tufail" wrote in message
...
I got it, yes i had in K7:K3000 minus mark like - so I removed that and
problam is solved, thanks for all of you !


"Bernard Liengme" wrote:

Please confirm:
1) D5 has an account number/name which can be found in L7:L3000 of sheet
Sold List
2) K7:K3000 is a list of numbers
If not please explain what you wish to accomplish
Have you checked they are all numbers. Does =COUNT(K7:K3000) yield 2994
?
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"Tufail" wrote in message
...
hi
i am trying to apply this below formula, but doesn't work well.
=SUMPRODUCT(('Sold List'!$L$7:$L$3000=D5)*('Sold List'!$K$7:$K$3000))
this error is coming out - #VALUE!
i want bring from Sold List value into Account List and D5 is in
Account
List
hope could be understand.
thanks in advance