Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Hi!
Try this: =SUMPRODUCT(--(A1:A100="xyz"),--(B1:B100=""),C1:C100) Biff "Dorian C. Chalom" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Biff wrote:
=SUMPRODUCT(--(A1:A100="xyz"),--(B1:B100=""),C1:C100) 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)) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Errata....
I wrote: =SUMPRODUCT((A1:A100="xyz")*(B1:B100=""),C1:C100) This form is easily extended to handle OR (replace "*" with "+"). 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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
So how would we handle the OR of the two conditions using SUMPRODUCT.
=SUMPRODUCT(--((A1:A100="xyz")+(B1:B100="")0),C1:C100) Biff wrote in message ups.com... Errata.... I wrote: =SUMPRODUCT((A1:A100="xyz")*(B1:B100=""),C1:C100) This form is easily extended to handle OR (replace "*" with "+"). 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. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Can someone tell me why the following array formula fails
=SUM(IF(AND(A1:A100="xyz",B1:B100=""),C1:C100)) 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 wrote in message oups.com... Biff wrote: =SUMPRODUCT(--(A1:A100="xyz"),--(B1:B100=""),C1:C100) 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)) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Biff wrote:
So how would we handle the OR of the two conditions using SUMPRODUCT. =SUMPRODUCT(--((A1:A100="xyz")+(B1:B100="")0),C1:C100) Klunk! Thanks. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf
Biff wrote:
Can someone tell me why the following array formula fails =SUM(IF(AND(A1:A100="xyz",B1:B100=""),C1:C100)) 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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to use SUMIF to return sums between two values located in cells | Excel Worksheet Functions | |||
Sumif of Sumif perhaps? | Excel Discussion (Misc queries) | |||
SUMIF | Excel Worksheet Functions | |||
Embedding a Sumif in a sumif | Excel Worksheet Functions | |||
SUMIF - Range name to used for the "sum_range" portion of a SUMIF function | Excel Worksheet Functions |