error in SUMPRODUCT

[Tufail]

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

 

[Bernard Liengme]

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

 

[daddylonglegs]

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

 

[Ragdyer]

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)

 

[joeu2004]

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.

 

[Tufail]

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

 

[Tufail]

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 !

 

[Tufail]

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 !

 

[Ragdyer]

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.