Ken said:
=SUMIF(Unique!$F$1:$F$500,BU!$A2,Unique!$M$1:$Y$500)
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.
