ExcelBanter - Sumif? ... Unexpected Results?

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Sumif? ... Unexpected Results? (https://www.excelbanter.com/excel-discussion-misc-queries/237738-sumif-unexpected-results.html)

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

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.

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