Oct 06 2021 04:34 AM
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".
Oct 06 2021 04:56 AM
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.
Oct 06 2021 05:03 AM
@StefanZechner From the help screens:
So, your formula is summing 100+200 for two rows that contain "Auto". Hence, 600.
Oct 06 2021 05:22 AM
Oct 06 2021 05:32 AM
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.