Looking for Explanation of SUMPRODUCT(--(Criteria), --(2nd Criteria))

Hi,

I was using COUNTIFS in Excel 2007 and searched for an equivalent that was compatible with 2003 when I stumbled upon SUMPRODUCT.

Please Login or Register  to view this content.

Does seem to work the same as:

Please Login or Register  to view this content.

Which is great... but I'm not content with it just working. I don't understand what the two dashes and extra parenthesis are meant to do. Maybe it's because it's late here that I'm not getting it, so if someone could elaborate I'd appreciate it.

The extra parens force Excel to evaluate the logical expression within it correctly. See Help for About calculation operators. You can see how the formula evaluates without them using the Formula Auditing toolbar.

The first "-" coerces the logical values True and False to -1 and 0 respectively; the second "-" converts the -1 and 0 to 1 and 0, the values useful in evaluating the rest of the expression.

You could also rewrite the second expression as

=SUMPRODUCT( (B3:B$65000=B2) * (H3:H$65000="Test"))

In this case, it is the multiplication operator that causes the True and False results to be coerced to 1 and 0.

There's a very good in depth explanation of SUMPRODUCT here:

http://www.xldynamic.com/source/xld.sumproduct.html

Dom