SUMMEWENN Bereich / SUMIF Range Bug

%3CLINGO-SUB%20id%3D%22lingo-sub-2817048%22%20slang%3D%22de-DE%22%3ESUMMEWENN%20Bereich%20%2F%20SUMIF%20Range%20Bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817048%22%20slang%3D%22de-DE%22%3E%3CP%3EIn%20the%20attached%20file%20you%20can%20find%20an%20easy%20example%2C%20which%20in%20my%20eyes%20is%20a%20bug.%20Had%20to%20search%20a%20long%20time%20in%20a%20real%20planning%20file%20to%20find%20out%20about%20it.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-csharp%22%3E%3CCODE%3E%3DSUMMEWENN(%24G%3A%24J%3BB4%3B%24J%3A%24J)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3Esums%20up%20not%20only%20values%20from%20column%20J%20in%20that%20example%2C%20but%20also%20from%20column%20K%20(which%20is%20out%20of%20any%20range%20given%20to%20the%20function)%20if%20B4%20matches%20any%20value%20in%20H.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20clear%20to%20me%20that%20the%20range%20should%20be%20just%20one%20column%2C%20but%20was%20accidently%20more%20columns.%20Anyway%20it%20should%20not%20sum%20up%20values%20not%20given%20in%20%22sum_range%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2817048%22%20slang%3D%22de-DE%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2817158%22%20slang%3D%22en-US%22%3ERe%3A%20SUMMEWENN%20Bereich%20%2F%20SUMIF%20Range%20Bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817158%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176943%22%20target%3D%22_blank%22%3E%40StefanZechner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESince%20you%20use%20two%20columns%20in%20criteria%20range%2C%20Excel%20expands%20in%20background%20sum%20range%20also%20on%20two%20columns%20and%20sums%20values%20in%20yellow%20cells%20here%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20501px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315571i187B5B70516C2D48%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EPlease%20use%20same%20size%20ranges%2C%20even%20better%201D%20ranges%20to%20avoid%20side%20effects.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2817168%22%20slang%3D%22en-US%22%3ERe%3A%20SUMMEWENN%20Bereich%20%2F%20SUMIF%20Range%20Bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176943%22%20target%3D%22_blank%22%3E%40StefanZechner%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DSUMMEWENNS(J%3AJ%3B%24G%3A%24G%3B%24B4%3B%24H%3A%24H%3B%24C4)%3C%2FP%3E%3CP%3EThe%20sumif%20formula%20cannot%20handle%201%20criteria%20for%20several%20criteria%20ranges.%3C%2FP%3E%3CP%3ESumifs%20can%20handle%20this%20task.%3C%2FP%3E%3CP%3EAbove%20sumifs%20formula%20can%20be%20entered%20in%20cell%20D4%20and%20copied%20across%20range%20D4%3AE5.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2817186%22%20slang%3D%22en-US%22%3ERe%3A%20SUMMEWENN%20Bereich%20%2F%20SUMIF%20Range%20Bug%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2817186%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1176943%22%20target%3D%22_blank%22%3E%40StefanZechner%3C%2FA%3E%26nbsp%3BFrom%20the%20help%20screens%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%202021-10-06%20at%2014.01.43.png%22%20style%3D%22width%3A%20432px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F315574i37E683CE9D41A7DC%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%202021-10-06%20at%2014.01.43.png%22%20alt%3D%22Screenshot%202021-10-06%20at%2014.01.43.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3ESo%2C%20your%20formula%20is%20summing%20100%2B200%20for%20two%20rows%20that%20contain%20%22Auto%22.%20Hence%2C%20600.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@StefanZechner 

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

image.png

Please use same size ranges, even better 1D ranges to avoid side effects.

@StefanZechner From the help screens:

Screenshot 2021-10-06 at 14.01.43.png

So, your formula is summing 100+200 for two rows that contain "Auto". Hence, 600.

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:D5

@StefanZechner 

You 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.