Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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... |
#2
|
|||
|
|||
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... . |
#3
|
|||
|
|||
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) |
#4
|
|||
|
|||
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... . |
#5
|
|||
|
|||
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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with sumproduct and month=1 | Excel Worksheet Functions | |||
Sumproduct Function problem | Excel Worksheet Functions | |||
Another Sumproduct & #N/A problem | Excel Worksheet Functions | |||
SUMPRODUCT Problem | Excel Discussion (Misc queries) | |||
SUMPRODUCT problem | Excel Worksheet Functions |