Thread: Whats wrong with this expression (VALUE).

Hi all,

Can anyone tell me why this expression fails with a #VALUE! Error:-

=IF(H18="","",SUMPRODUCT(($C$18:$E$397=H18)*($D$18:$F$397)))

If I've read up correctly, #VALUE! errors can be related to attempting to evaluate text and numeric cells within an expression. I am indeed trying to mix cells (Cols C, E & H are text, D, F and the receiving cell are numeric), however, I'm 100% sure it was working at one stage!.

I'm also getting a #VALUE! error with this expression on the same sheet:-

=I18*9-L18

but I think that is due to Col L being the receiving cell for the top expression, so I guess I need to fix the top one first.

Any pointers please.
J.

By the sound of it you could replace the SUMPRODUCT with a SUMIF which will avoid the issue of non-numeric coercion (column E being the issue in the SUMPRODUCT)

=IF(H18="","",SUMIF($C$18:$E$397,H18,$D$18:$F$397))

The SUMIF works and the SUMPRODUCT doesn't because of coercion.

Consider A1:D3

Code:

a	1	c	7
b	2	a	8
c	3	b	10

The below will generate a #VALUE! error

Code:

=SUMPRODUCT(($A$1:$C$3="a")*$B$1:$D$3)

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

Code:

=SUMPRODUCT(--($A$1:$C$3="a"),$B$1:$D$3)

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:

Code:

=SUMIF($A$1:$A$3,"a",$C$1:$C$3)

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

Code:

=SUMIF($A$1:$C$3,"a",$B$1:$D$3)

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.

Can anyone please tell me if the answer to the question below, is in the instructions above?

I'm sure the answer is there and I have read and re-read and think I even understand, but I can't get it to adapt to what I'm trying to do... and I'm going bonkers trying!

I have a similar expression as the first, with the addition of a simple evaluation, but I can't get it to return the numeric that it should. However I swap it around the best I can get is TRUE or FALSE.

I have these expressions:-

=IF(H18="","",SUMPRODUCT(($C$18:$E$397=H18)*($D$18:$F$397<>"")))
=IF(H18="","",SUMPRODUCT(($C$18:$E$397=H18)*($D$18:$F$397>=5)))

I've change them to SUMIF's like this:-

=IF(H18="","",SUMIF($C$18:$E$397,H18,$D$18:$F$397))<>””
=IF(H18="";"";SUMIF($C$18:$E$397,H18,$D$18:$F$397))>=5

I've tried moving the last evaluation before the last bracket. I've been off reading about ISERROR and tried enclosing the expression with that (don't know if that was a valid option, but I couldn't get it to work).

Am I missing the obvious again?!... I'm positive it's in the explanation above and I'm just a totally dummy for not seeing it.

You cannot convert those Sumproducts to Sumif because Sumif only takes one criteria range and one criteria.. you have 2 conditions (ie. 2 ranges and 2 criteria), therefore you need to use Sumproduct in this case.

If you have XL2007, then you could use SUMIFS which allows multiple criteria..

The samples by DonkeyOte and daddylonglegs above were all based on one condition, and one summing range.

Is it possible to see a sample workbook showing this issue?

There is also, the array formula that daddylonglegs mentioned... it is a combination of Sum() and IF()

If would be like this:

=IF(H18="","",SUM(IF($C$18:$E$397=H18),IF($D$18:$F$397<>””,1))

and

=IF(H18="";"";SUM(IF($C$18:$E$397=H18,IF($D$18:$F$397>=5,1))

both of these must be confirmed with CTRL+SHIFT+ENTER not just ENTER.

Try those first.