Forum Discussion
SUMMEWENN Bereich / SUMIF Range Bug
In the attached file you can find an easy example, which in my eyes is a bug. Had to search a long time in a real planning file to find out about it.
=SUMMEWENN($G:$J;B4;$J:$J)sums up not only values from column J in that example, but also from column K (which is out of any range given to the function) if B4 matches any value in H.
It is clear to me that the range should be just one column, but was accidently more columns. Anyway it should not sum up values not given in "sum_range".
4 Replies
- Yeah I understand that i am too stupid for Excel and the help file 🙂 As I said above the bigger range was not intended, but most other formulas would throw an error if ranges don't fit together and not just assume this kind of behaviour. And personally i doubt many users are aware of the fact that ranges larger than "sum_range" will be summed up.
SUMIF(A1:B5 ; "value"; C1:C5) :
range sum_range Actual summed cells
A1:B5 C1:C5 C1:D5You are not stupid for Excel, you only ned bit more experience. Some things could seems not logical, we simply shall know that works as it works, the point. No one of us knows everything about the Excel.
StefanZechner From the help screens:
So, your formula is summing 100+200 for two rows that contain "Auto". Hence, 600.
Since you use two columns in criteria range, Excel expands in background sum range also on two columns and sums values in yellow cells here
Please use same size ranges, even better 1D ranges to avoid side effects.
