Forum Discussion
SUMIFS using criteria in rows and columns
egspen2 Use INDEX/MATCH instead. Demonstrated it in your attached file with the help of some named ranges to make the formula easier to read/maintain.
Riny_van_Eekelen Thanks. I was curious if Index+Match+Match was going to be the response. Do I correctly infer then that SUMIFS is not able to do what I was trying to do? If so, can you briefly explain why?
egspen2 Haven't really thought about why not to use SUMIF. When I saw your file, I immediately thought about using INDEX and MATCH as you are looking to return an amount from an array in one sheet based on row and column headers in another.
Riny_van_Eekelen Makes sense. While I follow the concept of index/match / match and how you set it up in the example with the named ranges, I can't seem to get it to work when I tried copying over (see attached). My first thought is maybe there's something wrong with the named ranges or inconsistent formatting between tabs? Can't figure out why I'm getting $0 on rows 7 & 9 on the 'Consolidating' tab.
egspen2 Just changed some references in the named ranges. I also removed the repeated PL references in column B on the GMD Master, as MATCH finds the first one, and you only want the number on the row with the sub-total. Seems to work as desired.