Your description is not very clear. If H12:H14 are all equal to 1, and
the formula works, then F1 is presumably 1. You cannot change H12:H14
to TRUE without also changing F1 and still get a non-zero sum.

If instead of using the formula as written, you are experimenting with
array constants, as in
=SUMPRODUCT({1;1;1};{0;1234567;0})
(assuming that ; is the argument separator in your regional settings), then
=SUMPRODUCT({TRUE;TRUE;TRUE};{0;1234567;0})
should return zero, because there are no numeric values in the first
array. The function of the -- in the formula is to coerce logical
values to 0 (FALSE) or 1 (TRUE) so the multiplication can occur.
=SUMPRODUCT(--{TRUE;TRUE;TRUE};{0;1234567;0})
or
=SUMPRODUCT({TRUE;TRUE;TRUE}*{0;1234567;0})
would both work.

Jerry

Hiughs wrote:

Hi Biff... I've tried your recomendation but the result is zero and I don't
understand why... This formula is part of a bigger one and here is where is
failing... I wrote {1;1;1};{0;1234567;0} as values just to test it and works
but when i change in the first array 1 for TRUE the result is zero??? What do
you think?

"Biff" wrote:


Hi!

Try this:

=SUMPRODUCT(--($H$12:$H$14=F1),$G$12:$G$14)

OR

=SUMIF($H$12:$H$14,F1,$G$12:$G$14)

Biff


-----Original Message-----
I've troubles using sumproduct... In the functions

assistant the formula

result it´s fine, but when i hit the Ok button the

result is #VALUE why?...

This is the formula: =SUMPRODUCT((IF($H$12:$H$14=F1;1;0));

($G$12:$G$14))

Help please...