Please explain what the two neg signs are in Sumproduct(--

Re: Please explain what the two neg signs are in Sumproduct(

Here, from the master

http://www.mrexcel.com/board2/viewtopic.php?t=25380

Re: Please explain what the two neg signs are in Sumproduct(

As I understand it, the -- coerces, or changes whatever is in the parentheses (usually a range equal to, greater than, etc. some other value or range) to a TRUE or FALSE value.

Then the SUMPRODUCT() function (usually, anyway) takes all the TRUEs, and multiplies them together, giving you a range where only the criteria you've met are true.

Does that help some? I'm sure others will explain a little differently -- sometimes that's what it takes to "get" something.

Re: Please explain what the two neg signs are in Sumproduct(

Here's how I like to think of it:

For some reason, Excel does not seem to handle boolean expressions (True and False) the same way every time, at least when there is math involved.
- Sometimes True = 1 and False = 0 automatically.
- Sometimes it needs to be "coerced", meaning you have to do some math with it. (I'm still fuzzy on when it needs to be coerced, and when it doesn't, so I assume I need to coerce anytime I do math with booleans)
- The easiest Math (and according to what I've read on these boards, the fastest calculation wise) is --. -- 5 is still 5. It doesn't actually change it. Negative 1 x Negative 1 x BOB is still BOB. But as far as Excel is concerned, you have done a mathematical thing to it
For a Boolean, that means Trues and Falses are officially "Coerced" into a number, specifically:
--True becomes a 1, and --False becomes a 0

This just happens to come up a lot when using the SumProduct Function. The SumProduct needs numbers, so anything that is a boolean must be "coerced". The -- is not really part of the SumProduct Function, that is why you don't see it in the help menu for SumProduct()

The -- comes up a lot when using the SumProduct function, because often we like to use Booleans with the SumProduct. Like, if you've ever wanted to do a SumIf() that depends on more than one condition.

For Example: You have three columns of Data A, B, C (Sales, Day of Week, AM or PM)

You might want to add up the Sales for Monday Afternoons. Well...
=SUMPRODUCT(A1:A10,--(B1:B10="Monday"),--(C1:C10="PM"))

Notice then 2nd and 3rd Arrays have --, but the first does not.
The first is a number, so it does not need the --

Essentially, the formula works through the arrays:
If B1 is Monday and C1 is PM, then include A1 in the total
If B2 is Monday and C2 is PM, then add A2 to the total etc.

Really SumProduct performs a Dot product, multiply the components and adding them all together.
In this case that is A1 * B1 * C1 + A2 * B2 * C2 + ...

Because the B and C elements are Trues and Falses coerced to 1's and 0's. This statement effectively becomes a SumIf Statement with 2 conditions. If either the B element or the C element is zero, then it doesn't matter what value is in A, because A * 0 is zero. If B and C are both True (coerced to be 1), then the A gets included in the sum. Notice without coercion, you will always get 0. (You could add a lot more by adding more Arrays, I suppose in principle you could have 10 or more conditions, that's pretty cool.)

The SumProduct function is far more than a compound SumIF function, but that is the way I've used it most. Really you could perform any operation to the elements of each array, it doesn't have to be = something, you could square them, halve them or perform most mathematical operations I think.

I think I'm expressing some ideas I've been thinking about lately. I hope this helps someone out there.

Re: Please explain what the two neg signs are in Sumproduct(

Thanks to all for the excellent explanations.