Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif? ... Unexpected Results?
Excel2003 ... Sumif?
=SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$50 0) Is only returning the SUM of Values found in Col "M"??? Above said ... the values in Range F1:F500 are NOT contiguous ... Please provide guidance ... Thanks ... Kha |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif? ... Unexpected Results?
Look in the help index for SUMIF -- Don Guillett Microsoft MVP Excel SalesAid Software "Ken" wrote in message ... Excel2003 ... Sumif? =SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$50 0) Is only returning the SUM of Values found in Col "M"??? Above said ... the values in Range F1:F500 are NOT contiguous ... Please provide guidance ... Thanks ... Kha |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif? ... Unexpected Results?
"Ken" wrote:
=SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$50 0) Is only returning the SUM of Values found in Col "M"??? Well, I'm surprised it does not return a #VALUE error, as SUMPRODUCT does under similar circumstances. Apparently, SUMIF only sums over a single vector of cells specified by the 3rd argument; i.e. M1:M500 above. Presumably, you wanted it to sum M1:Y1 if F1=A2, M2:Y2 if F2=A2, etc. I do not see anything in the Excel 2003 SUMIF help page that precludes your expectation. The help page says only: "The cells in ``sum_range`` are summed only if their corresponding cells in ``range`` match the criteria" (`` added for clarity). Certainly the entire range M1:Y1 corresponds to F1. But apparently that is not how SUMIF is defined. So be it. I have not found a brief alternative way for you to express what you want, if my presumption is correct. Certainly you could write: =sumproduct(--(Unique!$F$1:$F$500=BU!$A2), Unique!$M$1:$M$500 + Unique!$N$1:$N$500 + ... + Unique!$Y1:$Y$500) But that is too tedious even for me to write here. (You would not write literally "+...+". I used to take it for granted that people would know that. But I learned that many people do not.) I suspect there is a clever array formula to express the desired conditional row-wise sum that I presume you want. I have not found one yet. Hopefully someone will offer a constructive response. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Sumif? ... Unexpected Results?
=SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$50 0)
Yes, although Excel accepts the above expression as-is, in actuality only the leftmost col M is operative, as you found out Try using sumproduct for multi-cols: =SUMPRODUCT((Unique!$F$1:$F$500=BU!$A2)*Unique!$M$ 1:$Y$500) Above lightly tested ok. If it works, click the YES below -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Ken" wrote: Excel2003 ... Sumif? =SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$50 0) Is only returning the SUM of Values found in Col "M"??? Above said ... the values in Range F1:F500 are NOT contiguous ... Please provide guidance ... Thanks ... Kha |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
lookup formula unexpected results | Excel Worksheet Functions | |||
lookup formula unexpected results | Excel Worksheet Functions | |||
UNexpected NPER results | Excel Discussion (Misc queries) | |||
Help please... Simple calculation - with unexpected results | Excel Worksheet Functions | |||
Equation giving unexpected results | Charts and Charting in Excel |