Sum range between two index-matched values
Use the following:
=SUM(INDEX(B2:E4,MATCH(C8,A2:A4,0),MATCH(C6,B1:E1,0)) : INDEX(B2:E4,MATCH(C8,A2:A4,0),MATCH(C7,B1:E1,0)))
To use INDEX()
to return a reference, not a value, you must use it in a way that Excel can only make sense of if it must return a reference. The use of a full lookup, then the colon, then another full lookup gives Excel the clue it needs. So it makes a range of the two cells located and passes that to the SUM()
function wrapping around everything else.
Also (and this might be an artifact of making a sample set of data, but using your formula right from your spreadsheet with just cosmetic adjustments) your formula will not work as you have the matches backwards in the INDEX()
formula. It requires ROW and then COLUMN to follow the range and you are giving it COLUMN, then ROW. If it is actually as you used it, any "incorrect" results you got in using it would almost certainly be due to that.