Pete_UK said:
The condition (A1:A4="x") gives an array of TRUE
and FALSE, but these can't be used in arithmetic.
I beg to differ with you. If that were true, then --(A1:A4="x") would not
work (i.e. behave as intended) since that is an arithmetic expression.
In fact, the array formula SUM((A1:A4="x")*B1:B4) works just fine. So does
the equivalent non-array formula SUMPRODUCT((A1:A4="x")*B1:B4). Both use
arrays of TRUE and FALSE in the arithmetic.
SUMPRODUCT((A1:A4="x"),B1:B4) does not work because the designers of
SUMPRODUCT made the arbitrary decision that SUMPRODUCT will treat arrays
arguments "that are not numeric as if they were zeros". See the SUMPRODUCT
help page.
Similarly, the array formula SUM((A1:A4="x"),B1:B4) does not have the same
result as the array formulas SUM(--(A1:A4="x"),B1:B4) and
SUM((A1:A4="x")+B1:B4). Again, the designers of SUM made the arbitrary
decision that "
f an argument is an array or reference, [... e]mpty cells,
logical values, text, or error values in the array or reference are ignored"
(i.e. treated as zero), as the SUM help page explains.
Note: When I say "arbitrary", I do not mean to imply that their decision
was capricious, at least not for Excel. I simply mean that they made a
choice; it is not something fundamental to computer logic. In fact, to some
degree, the choice in Excel was probably made to be compatible with
market-leading competitors and earlier MS products (e.g. Multiplan).
The double unary minus, --, converts
these to an array of 1s and 0s
That is correct.
But just so there is no misunderstanding, there is nothing special about the
double negation ("--"). Any value-preserving arithmetic operation would
accomplish the same thing, e.g. (A1:A4="x")*1 and (A1:A4="x")+0.
The result of -(A1:A4="x"), single negation, or any other arithmetic
operation is an array of numerical values. Double negation is needed to
restore the original numerical values, just --5 is 5.
----- original message -----
The condition (A1:A4="x") gives an array of TRUE and FALSE, but these
can't be used in arithmetic. The double unary minus, --, converts
these to an array of 1s and 0s, which can be, and each of these is
then multiplied by the array elements in B1:B4.
Incidentally, as you only have one condition then SUMIF would be a
better function to use:
=SUMIF(A1:A4,"x",B1:B4)
Hope this helps.
Pete
