SUMIF is a handy but helpful function. The syntax is simple:
What it does is quite straight forward indeed. In the example above, it instructs Excel to look into the range (A2:A7), look for the matching criteria, which is “A“, and then sum the “corresponding” value in the sum range (B2:B7). That’s how we get 5 as a result. Make sense?
However, if we point to incorrect reference for sum range, SUMIF may give you an unexpected (and also incorrect) result. What do I mean? Let’s say if I mistype (by human error of course) the formula to
=SUMIF(A2:A7,D2,B3:B8) 'Note the one cell shift in the sum range
Do you expect a result of 4?
Surprisingly the result is 7.
You may wonder HOW?
Now let’s look at another example. What if I keep the same “mistyped” formula but changed the criteria in D2 to “B”.
Do you expect 7 as a result?
Yes. I do. But Excel returns 9.
So what happened? Is it a bug?
No. It’s a feature indeed. 🙂
Below is an official “remark” for the function SUMIF:
The sum_range argument does not have to be the same size and shape as the range argument. The actual cells that are added are determined by using the upper leftmost cell in the sum_range argument as the beginning cell, and then including cells that correspond in size and shape to the range argument.
Full description at https://support.office.com/en-us/article/SUMIF-function-169b8c99-c05c-4483-a712-1697a653039b
What? “Does not have to be the same size and shape“? What is “upper leftmost“? What is “correspond in size and shape…“?
Let’s illustrate it using our example:
For this formula, Excel first looks into the range A2:A7 for a match to D2 (“B”).
Where are the matches? They are the 2nd and 5th position of the range.
Now Excel goes to the argument set for sum range (B3:B8), which is essentially B3 (the upper leftmost), then determines the “actual” sum range by setting a range of “correspond in size and shape” from B3, i.e. B3:B8 because the original range Excel looked into is a 1D vertical range consisting of 6 cells. (Try: change the sum range to B3:B4)
And now is the tricky part: Excel adds the value in the “corresponding” position(s), i.e. 2nd and 5th values, in the sum range, which are 3 and 6 respectively.
Now it makes sense that SUMIF returns the result of 9, instead of 7.
Put it in other word, the range and the sum range do not necessarily sit together side by side. You may arrange your data in the following way to have SUMIF worked…
although it is not “human-friendly” at all.
Wait… it is so confusing, isn’t it? Why Excel has such “feature” for SUMIF?
Because it gives you the flexibility to perform dynamic SUMIF.
Anyway, I guess most people do not appreciate this “feature” of allowing different size and shape of the sum range from the range, and it is no longer available in SUMIFS.
Having said that, you still need to pay attention to the “position” of the sum range,
as both SUMIF and SUMIFS are following the same logic in a way… 😛
Hi, I use sumif function to total multiple number of columns that has same ID on the a reference cell but the total is not correct if I double check using sum(row by row). Its a table. formula like =sumif(C3:Q400,A1,AR3:AR400)
Pingback: Be cautions when using XLOOKUP | wmfexcel
Just have to give you thanks. Looked for this answer everywhere and no one really got to the core of it. A lot of solutions like do you have additional data later in your table etc etc. But you fixed my issue and now I understand the logistics behind it.
You are welcome. Glad to help. It makes a difference when we truly understand how a formula/function works 😀
So that is another great reason to use the Excel table feature. I once helped someone troubleshoot their report that was returning incorrect answers. It was this exact cause of the two ranges being offset by one row. How it happened, I can’t say, but likely a cell got inserted above one of the ranges at one time.
Besides the ease of use of a table, I have found spreadsheets are far less prone to errors when using tables.
This is a very good suggestion!