Using "--" in a SUMPRODUCT formula

[6StringJazzer]

I saw an example in another forum that used "--" like this:

=SUMPRODUCT(--($A$2:$A$9=$J$2),--($B$2:$B$9=$K$2),(D$2:D$9))

What does qualifying the array expression with "--" do?

In this case the arrays in the first two expressions (columns A and B) contain text. The formula is seeking a match for the two text columns then returning another column (D) with corresponding data.

I do not have a specific problem to solve but am wondering about this usage.

[DonkeyOte]

-- is referred to as double unary coercion

A Boolean True/False can be coerced to it's integer equivalent 1/0 respectively in a number of ways, the most common being:

Please Login or Register  to view this content.

The double unary is opted for by many as it is deemed as perhaps the more efficient of the options ... (using division is limited to specific situations - ie 1/FALSE generally leads to #DIV/0! errors)

Some have/do argue for single unary coercion given less operations required to coerce but it means being aware how of how many "arrays" of values are being coerced - ie if the number is odd the result will be returned in the opposite sign to the correct value.

For a good overview of SUMPRODUCT and coercion see the link in my sig. to Bob Phillips' white paper.

[6StringJazzer]

Thanks for a quick reply! I didn't realize explicit conversion was needed when the context used a boolean as an integer. The link was informational too. I have a software development background but coercion policies can vary widely......thanks again.