I have a question about Excel & VBA’s function SUMIFS(). I have two codes, but I changed the input-places from one to another (Århus & Odense, but 2 and 3 could also be used):
I need to find the correct sum when these criterias are used. I have tried to google and tried to understand SUMIFS. I tried to simulataing another dataset, with the same amount of variables and changes the different input locations. However, when comparing the 4 difference input places I get the same result
Code 1)
SUM(SUMIFS($D$2:$D$2000;$B$2:$B$2000;{"Odense";"Århus"};_
$C$2:$C$2000;{2;3};$E$2:$E$2000;ABS(I16)))
Code 2)
SUM(SUMIFS($D$2:$D$2000;$B$2:$B$2000;{"Århus";"Odense"};_
$C$2:$C$2000;{2;3};$E$2:$E$2000;ABS(I16)))
Code 1 gives 152832 and Code 2 gives 135751. So I hope that anyone can explain to me why this happen. Or maybe that there is something wrong with the data that is being used.
when using two arrays in SUMIFS if both arrays are vertical or both horizontal then it will matter as it will only do two and compare one to one stepping through each array the same.
If you want to do OR on both arrays then one must be Vertical and the other Horizontal:
SUM(SUMIFS($D$2:$D$2000;$B$2:$B$2000;{"Århus";"Odense"};$C$2:$C$2000;TRANSPOSE({2;3});$E$2:$E$2000;ABS(I16)))
Also note that when not in lockstep the max on the OR type is two arrays.