Need help understanding sumifs columns with multiple criteria

[igoodable]

Hi all,

After some research, I've stitched together this formula that works perfectly, but I don't completely understand the logic. Could some one review the workbook attached and explained to me the logic that makes it works?

The formula sums multiple columns with multiple criteria associated with the columns.

formula:
SUM(SUMIFS($A2:$I2,$C2:$K2,{"A","B","C","D"}))

Questions:
1. How does excel knows to correspond A2 TO condition c2, e2 to g2, and i2 to k2?
2. How does excel knows to ignore other numbers in the range? For example in the workbook, excel knows to correctly excludes f7 from the calculations.
3. How does excel knows to ignore matching criteria not in range? For example in the workbook, d4 is correctly excluded but it meets the criteria, but not range.

thanks,

[6StringJazzer]

This is a very tricky formula. You may want to read the Help page on SUMIFS. The first argument is the range to sum. The second argument is a range which is parallel to the first range, and is compared to the criteria in the last argument to determine whether each element of the first range is to be included in the sum.

The answer to #1 is that based on the definition of how SUMIFS works, $A2 is included in the sum if $C2 meets the criteria, and likewise B2/D2, C2/E2, and so forth to I2/K2.

The answer to #2 is that in this data, the only pairings that really matter are A2/C2, E2/G2, and I2/K2. That is because all the other cells either do not have numeric data, or will not match the letters in the formula. It's a tricky way to do this, because it depends on the specific organization of the data and is not obvious. It's easier to write the formula this way to span the entire range, even though only a few cells in that range are relevant, but harder to understand what it's doing.

(I don't understand question #3 but it might be answered if you understand the above.)

Now comes the tricky part. The comparison to the array {"A","B","C","D"} means that SUMIFS will return an array of results instead of a single result when it compares each cell in the second argument with each value of this array. That's why you need to have SUM around this, to sum the array returned by SUMIFS. This is undocumented behavior of SUMIFS, but is true of several other Excel built-in functions.