Help with the Double unary operator

**amartino44** · 09-05-2013, 01:18 PM

Hello. I was reading about the double unary operator and sumproduct formulas and I don't really understand it. Someone helped me with a formula and he came up with the following. Could someone explain what these formulas are doing? Also, what would happen if I took the unary operator out of the first formula? Lastly, why does the second formula not need a double unary operator? Danks.

=SUMPRODUCT(--(Data!$A$5:$A$13=Sheet2!$A$3)*(Data!$B$1:$Y$1=Sheet2!B2),Data!$B$5:$Y$13)

=SUMPRODUCT((Data!$A$5:$A$13=Sheet2!$A$3)*(Data!$B$2:$Y$2=Sheet2!C2)*(Data!$B$1:$Y$1=Sheet2!B2),Data!$B$5:$Y$13)

**shg** · 09-05-2013, 01:30 PM

When Boolean values are part of any arithmetic expression, True is coerced to 1 and False to 0. The double negation is one such arithmetic operation, but so is the multiplication inside both of your sumproduct functions, so it isn't necessary. You need it in cases when there is no other operation, e.g.,

=sumproduct(--(a1:a10="Bob"))

**amartino44** · 09-05-2013, 02:51 PM

So, in the formula above the double negation is unnecesarry? What does the formula that you have do? Don't you need two sets of data for a sumproduct?