Problem using sumproduct
 

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...

Hi!

Try this:

=3DSUMPRODUCT(--($H$12:$H$14=3DF1),$G$12:$G$14)

OR

=3DSUMIF($H$12:$H$14,F1,$G$12:$G$14)

Biff

-----Original Message-----
I've troubles using sumproduct... In the functions=20
assistant the formula=20
result it=C2=B4s fine, but when i hit the Ok button the=20
result is #VALUE why?...=20
This is the formula: =3DSUMPRODUCT((IF($H$12:$H$14=3DF1;1;0));
($G$12:$G$14)) =20
Help please...
.

Hiughs wrote:
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...

You don't need SumProduct for you just have one condition. That means
you can invoke a faster ordinary formula:

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

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...
.

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...