PDA

View Full Version : 2 Sumproduct Function same result

hardeep
07-09-2009, 12:58 AM
What is the Difference Between these 2 Sumproduct Functions.

SUMPRODUCT(--(\$H\$2:\$H\$33=H2),--(\$I\$2:\$I\$33=I2),--(\$J\$2:\$J\$33))

SUMPRODUCT((\$H\$2:\$H\$33=H2)*(\$I\$2:\$I\$33=I2),\$J\$2:\$J\$33)

Name City Total 1st Sumproduct 2nd Sumproduct A X 7.8 24.06 24.06 A X 6.8 24.06 24.06 A X 5.23 24.06 24.06 A X 4.23 24.06 24.06 A Y 12 12 12 D Y 24 47 47 D Y 23 47 47 G X 24.3 24.3 24.3 J Y 12 68 68 J Y 56 68 68 J X 48 112 112 J X 12 112 112 J X 23 112 112 J X 15 112 112 J X 14 112 112 M X 12 40 40 M X 13 40 40 M X 15 40 40 P Y 12 38 38 P Y 14 38 38 P Y 12 38 38 S X 16 35 35 S X 19 35 35 V Y 17 56 56 V Y 18 56 56 V Y 21 56 56 X Z 22 158 158 X Z 23 158 158 X Z 26 158 158 X Z 28 158 158 X Z 29 158 158 X Z 30 158 158

both Give me Same Result

What is the Difference Between "--" and "*" in these Functions

And Which one is the Best or Sufficient.

regards

Hardeep kanwar

Bob Phillips
07-09-2009, 03:50 AM
Nothing in this case. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Juba
07-11-2009, 10:58 PM
Xld
i wan to be sure
the first - to convert any Mainus to positive
but the second Minus what do ?????????

mdmackillop
07-12-2009, 02:09 AM
Hi Juba
A2 = A1 can return True or False (say it it is true). Adding -- or *1 will coerce this to a numerical value.
-(A2=A1) = -1
--(A2=A1) = 1
The Double Unary therefor converts an array of True/False to an array of 1/0 which can be multiplies angainst an array of similar/other numerical values.

Bob Phillips
07-12-2009, 05:29 AM
Xld
i wan to be sure
the first - to convert any Mainus to positive
but the second Minus what do ?????????

No, the first minus to coerce True/False to -1/0, the second to make it positive.

Zack Barresse
07-16-2009, 01:32 PM
The way I always looked at it, is if you are within the same array and doing a mathematical operation, if it's a boolean return it will be coerced, thus no need for a double unary minus, or multiplied by 1, or added zero, whatever.

Nothing in this case. See http://www.xldynamic.com/source/xld.SUMPRODUCT.html
Your website is re-directing me to some weird site...