#### Mac XL

This site will look better if you upgrade to a browser that supports web standards.

# Why use -- in SUMPRODUCT formulae

SUMPRODUCT() ignores non-numeric entries. A comparison returns a boolean (TRUE/FALSE) value, which is non-numeric. XL automatically coerces boolean values to numeric values (1/0, respectively) in arithmetic operations (e.g., TRUE + 0 = 1).

The most efficient way to coerce the value is first to apply the unary minus operator, coercing TRUE/FALSE to -1/0, then applying it again to negate the value, e.g., +1/0.

The array is then numeric and will be evaluated by SUMPRODUCT().

## An example

Say you have the formula

`    =SUMPRODUCT(--(A1:A5>10),B1:B5))`

and the following values in A1:B5:

```           A          B
1     20          2
2      5          7
3     10          5
4      8          3
5     11          6
```

The first term (A1:A5>10) evaluates to

`    {TRUE,FALSE,FALSE,FALSE,TRUE}`

The first unary minus coerces the array to

`     {-1, 0, 0, 0, -1}`

The second negates the first

`     {1, 0, 0, 0, 1}`

So the SUMPRODUCT() evaluates

`     =SUMPRODUCT({1,0,0,0,1},{2,7,5,3,6})`

returning (1 * 2) + (0 * 7) + (0 * 5) + (0 * 3) + (1 * 6) = 8.