Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf

For reason known only by the Excel developers, the Boolean operators cannot
be used in array formulas.

ASIDE: Please note "array formulas" not "matrix formulas" in English
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"ergo" wrote in message
ps.com...
Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf

Also known by me, Bernard! -- if ANY of the items in the AND yield FALSE,
the whole result is FALSE, rendering the usefulness of AND(...) useless in
this kind of formula!
Actually, I know you know that too!
Bob Umlas


"Bernard Liengme" wrote in message
...
For reason known only by the Excel developers, the Boolean operators
cannot be used in array formulas.

ASIDE: Please note "array formulas" not "matrix formulas" in English
best wishes
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"ergo" wrote in message
ps.com...
Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf

"Bernard Liengme" wrote...
For reason known only by the Excel developers, the Boolean operators cannot
be used in array formulas.
....

They're *NOT* operators, they're functions. And there lies yet another
really bad Excel design decision. It's a function that HAS TO return a
SINGLE value. And that's what makes it effectively useless in array
formulas.

If you highlight the relevant parts of the formulas and then press f9 you
will see what thatpart of the formula resolves to.

The AND() will only be TRUE if all four cells are TRUE whereas the
multiplications are done individually.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"ergo" wrote in message
ps.com...
Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf

the logical statements are not identical
the first is the same as
(A1="a")*(B1="d")*C1 +(A2="a")*(B2="d")*C2
(1)(0)(1)+(1)(1)(2)=2

the second
(A1="a")*(B1="d*(A2="a")*(B2="d")*C1+(A1="a")*(B1= "d*(A2="a")*(B2="d")*C2
(1)(0)(1)(1)(1)+(1)(0)(1)(1)(2)=0



"ergo" wrote:

Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf

Olaf

Evaluating the second formula a piece at a time (by highlighting the
innermost parts of the formula and evaluating with F9) you can see
that a1:a2="a" evaluates to True, True; then B1:B2="d" evaluates to
False, True and therefore AND evaluates to False; hence the IF formula
evaluates to 0 and then the SUM is zero. In the first formula the
True, True and the False, True are multiplied, giving you a 0,1, which
is further multiplied by the 1,2 in C1:C2 leading to SUM evaluating to
2.

Ken



On Jun 25, 8:55 am, ergo wrote:
Hi,

please consider the following example:

a c 1
a d 2

Why is (as matrix formula)

=SUM(IF((A1:A2="a")*(B1:B2="d");C1:C2;0))

(which is 2) different from (as matrix formula)

=SUM(IF(AND(A1:A2="a";B1:B2="d");C1:C2;0))

(which is 0)? I would expect 2 in both cases.

Rregerds Olaf