Sumproduct error

[niting]

Hi forum,


I was just about to upload a worksheet where I was facing problem when I came across this post

http://chandoo.org/wp/2010/11/09/2way-lookup-formulas/ which gave me solution to my problem. My question for better understanding of the problem is-:


SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6),(MAY!$B$6:$BJ$79)), the formula gives the correct answer, whereas,

SUMPRODUCT((MAY!$B$5:$BJ$5=F$5),(MAY!$A$6:$A$79=$A6),(MAY!$B$6:$BJ$79)), this formula gives #Value error.


How does a simple"*" operator in place of "," gives correct answer.


Would appreciate if d forum members could explain the reason for the error.


Thanks

Niting

 

[Luke M]

The * forces XL to multiply the arrays all together. The complexity comes from the fact the the three arrays by themselves are different sizes. If you have studied, matrix multiplication, you'll remember that matrices have to fit specific criteria in order to be multiplied against each other. The first example works because you force XL to multiply the arrays as matrixes in such as way that the columns and rows work out correctly. With the commas, SUMPRODUCT tries to match every number in each array with a corresponding number in the other arrays. Since they are different sizes, it errors out.

 

[niting]

Luke,


that is exactly where I am getting stuck. I had used * operator for multiplication of array 1Rx61C with 74Rx1C giving me 74x61 array. However, when I use * operator for last array which is 74x61 size, then also XL throws #Value error i.e if i use

SUMPRODUCT((MAY!$B$5:$BJ$5=F$5)*(MAY!$A$6:$A$79=$A6)*(MAY!$B$6:$BJ$79)), i again get the error.


When I am multiplying the arrays of same size, then why do I get the error. I modified my formula as per Hui suggestion in the post http://chandoo.org/wp/2011/05/26/advanced-sumproduct-queries/, but I got the solution only by trial and error. I have still not understood the logic.


Thanks

 

[Luke M]

I got confused. In your first post, you stated that this formula:

SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6),(MAY!$B$6:$BJ$79))


does not give an error, but then your last post says that

SUMPRODUCT((MAY!$B$5:$BJ$5=F$5)*(MAY!$A$6:$A$79=$A6)*(MAY!$B$6:$BJ$79))


gives an error. As the only different is which cell the first array is looking at, I'm not sure why one works and the other doesn't. Can you clarify?

 

[niting]

Luke,


SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6)*(MAY!$B$6:$BJ$79))


gives error , whereas


SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6),(MAY!$B$6:$BJ$79))does not. it gives the correct value.


As I mentioned in the last post, when I am using the "," operator for last array, the formula works whereas using "*" operator, the formula throws error.


If you could explain, why it is happening


Thanks

 

[Montrey]

try entering the first one as an array with Ctrl+shift+enter

 

[Haseeb A]

SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6)*(MAY!$B$6:$BJ$79))


The above formula only work If MAY!$B$6:$BJ$79 contains ONLY NUMBERS. If there is ANY TEXT in this range will give error, even a 'space' or a formula result like ="" will cause error. Because this way TRUE/FALSE values are multiplying by TEXT.


If you try =1*"A" will give you error, or 1*"", 1*" " will give error. But if you use =1*"1" or 1*1 will work even if numbers are stored as text.


SUMPRODUCT((MAY!$B$5:$BJ$5=E$5)*(MAY!$A$6:$A$79=$A6),MAY!$B$6:$BJ$79)


The above formula will IGNORE all text entries in the range MAY!$B$6:$BJ$79


See the below link about SUMPRODUCT.


http://www.xldynamic.com/source/xld.SUMPRODUCT.html


Hope this helps

 

[Faseeh]

Hi niting,


I think you have got some "Non-Numeric Character" in your sum range i.e

(MAY!$B$6:$BJ$79)

.


The Logic behind the though is that your first formula conducts a logical check for both two conditions and then multiply the resulting 1/0 type output to the sum range. But if the formula founds a #Value Error it will not sum up things and give you a value error.


The Second formula that is not giving this error is because once it is done with multiplication of first two arrays, it remains simply as SUM() and that it simply ignores non numeric character.


The Multiplication of a Numeric and a Non Numeric value gives you Value Error.


Regards,

Faseeh