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.

This page last updated Tuesday, 17 May 2005

© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.