SumIf

D

Dorian C. Chalom

What if i want to sum on the third column based on the value in columns 2
and 3

i.e.
column1 must = "XYZ"
and coulmn2 must be blank
 
J

joeu2004

Biff said:
=SUMPRODUCT(--(A1:A100="xyz"),--(B1:B100=""),C1:C100)
Click to expand...

Or:

=SUMPRODUCT((A1:A100="xyz")*(B1:B100=""),C1:C100)

This form is easily extended to handle OR (replace "*" with "+"). How
could it be done using Biff's paradigm?

Can someone tell me why the following array formula fails (sum is
zero):

=SUM(IF(AND(A1:A100="xyz",B1:B100=""),C1:C100))

yet the following array formula works:

=SUM(IF((A1:A100="xyz")*(B1:B100=""),C1:C100))
 
J

joeu2004

Errata....
=SUMPRODUCT((A1:A100="xyz")*(B1:B100=""),C1:C100)

This form is easily extended to handle OR (replace "*" with "+").
Click to expand...

Wrong! That would not work if both conditions are true.

So how would we handle the OR of the two conditions using SUMPRODUCT.
Sigh, I am much too tired after driving for 5 hours after a couple of
sleepless nights.
 
B

Biff

So how would we handle the OR of the two conditions using SUMPRODUCT.

=SUMPRODUCT(--((A1:A100="xyz")+(B1:B100="")>0),C1:C100)

Biff
 
B

Biff

Can someone tell me why the following array formula fails
=SUM(IF(AND(A1:A100="xyz",B1:B100=""),C1:C100))
Click to expand...

AND requires every element to be TRUE. So, if every cell in A1:A100 = xyz
and every cell in B1:B100 = "" then it would work.

Biff
 
J

joeu2004

Biff said:
AND requires every element to be TRUE. So, if every cell in A1:A100 = xyz
and every cell in B1:B100 = "" then it would work.
Click to expand...

Well, yes. But I thought that an array formula of that form would be
evaluated like:

sum({if(and(a1="xyz",b1=""),c1,0), if(and(a2="xyz",b2=""),c2,0),...}).

In other words, I thought the Excel expression evaluator would "unwind"
the range references in the IF and AND parameters. I thought that is
why we must enter this formula with ctrl-shift-Enter instead of Enter.

I have relied on that assumption when writing other array formulas, for
example:

={exp(stdev(ln(a1:a99/a2:a100)))-1}

the formula I use to compute the std dev of the log returns of stock
prices. I expect that formula to be (and indeed it seems to be )
evaluated as:

exp(stdev({ln(a1/a2), ln(a2/a3),...})) - 1

But now I do see the syntactic ambiguity with the non-array-formula
form of AND. For example, =AND(A1:A10<11,B1:B10<11), entered with
Enter instead ctrl-shift-Enter, is true when all of A1:B10 are less
than 11, and false otherwise, as you say.

So is this simply a case of Excel resolving the syntactic ambiguity
differently than I expected?

And perhaps I should have expected it. I had not given any thought to
the fact that AND(rangeExpression) has meaning as non-array formula.
In contrast, apparently ln(rangeExpression) does not (fails with a
#Value error).

Hmm.... AND(range1Expression,range2Expression) does fail as a
non-array formula if the two ranges overlap. Klunk!

As this point, I better stop yapping and start listening, lest I dig
myself deeper into my own sh*t. I wish I had an academic understanding
of Excel expression evaluation and array formula evaluation in
particular.

Any insights about my ramblings would be greatly appreciated.
 
Top