The SUMIF works and the SUMPRODUCT doesn't because of coercion.
Consider A1:D3
The below will generate a #VALUE! error
Why ?
Well the first array of values can be seen as
{TRUE,FALSE,FALSE;FALSE,FALSE,TRUE;FALSE,FALSE,FALSE}
ie basic Booleans based on your test
The second array of values can be seen as
{1,"c",7;2,"a",8;3,"b",10}
ie the values within B1:D3
Now we know that we can "coerce" Booleans to numerical equivalent by a number of methods - one of which is of course multiplication... so in the SUMPRODUCT we look to multiply array one by array 2 to give us our output ... this can thus be viewed as
{TRUE*1,FALSE*"c",FALSE*7;FALSE*2,FALSE*"a",TRUE*8;FALSE*3,FALSE*"b",FALSE*10}
Perhaps know the issue will seem obvious... you can't explicitly coerce a non-numeric string to a number, ie
TRUE*"a" -> #VALUE! ... equiv. to =1*"a"
Given that's what you're doing the SUMPRODUCT returns #VALUE! - it won't simply ignore the errors.
It's perhaps worth pointing out at this point that given the Arrays used are of the same dimensions (3x3) you could revert to a double unary approach which would not explicitly coerce the summation range in this manner - eg
When it comes to the final evaluation the SUMPRODUCT will simply ignore non-numerics in your summation range (ie C1:C3).
(why use * rather than -- ? well the * method does have some distinct advantages over the -- method - discussed in Bob Phillips' white paper as linked in my sig.)
The double unary treatment of the summation range is in essence the same as that conducted by a SUMIF - this can be illustrated with a basic example of:
would simply yield 0 (not #VALUE!) - the values in the summation range have no numerical value.
It just so happens in this instance - given the layout of your data - that you could use
Rather than the SUMPRODUCT - the sum_range in the SUMIF will by default assume same dimension as the (criteria) range - which in this case is 3x3
The SUMIF will by the design of the formula look for matches of "a" in A1:A3, B1:B3, C1:C3 and sum associated values in column to the right (ie B1:B3,C1:C3,D1:D3 respectively)
Given "a" will not exist in B1:B3 this is not an issue - ie the 2nd range will always return 0... and thus all that takes place is in effect
The layout of the above is not the same as your example but the underlying principles are identical.
LinkBack URL
About LinkBacks
Register To Reply
.
)
Originally Posted by NBVC
Bookmarks