This site will look better if you upgrade to a browser that supports web standards.
This site will look better if you upgrade to a browser that supports web standards.
McGimpsey & Associates This site will look better if you upgrade to a browser that supports web standards.
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().
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
© Copyright 2001 - 2004 McGimpsey and Associates. Except where noted, all code on this site may be distributed under the Gnu GPL. Acknowledgement is appreciated.
