Returning array for use in SUMIFS formula

[jkerrigan93]

Hi,

I'm trying to write a simple SUMIFS formula and am using INDEX MATCH to pull in an array to use as one of the criteria. However instead of evaluating the entire array, the criteria is only using the first matched row. I know wrapping the SUMIFS formula in SUMPRODUCT usually does the trick, but doesn't seem to be working here.

Basically I would like map 'Item A' to 'Group A' and 'Group B' and then for each 'Type' sum up the values. So in the third table, row Type A and column Item A would sum up to 39 (10 + 15 + 14).

I've tried the following formula in column I

=SUMPRODUCT(SUMIFS(Table1[Value], Table1[Type],[@Type], Table1[Group], INDEX(Table2, MATCH(Table3[[#Headers],[Item A]],Table2[Item],0), MATCH("Group", Table2[#Headers],0))))

However as explained above, only the first value (Group A) is matched. This means for 'Type A' the value is only 25 (10 + 15), when instead it should be 39 (10 + 15 + 14). I know if I replace the index with the range I'm trying to pull, the formula works perfectly.

Also this is for Excel 2016. I have a feeling the above formula would work with Excel 365 but I need something 2016 friendly.

Any help is appreciated. Thanks.