This is a good alternative for the INDEX function combined with MATCH, when your data source consists of non unique values, and you need a sum instead of a specific value.
As the SUMIF does not work with column numbers (as in 1,2,3) but with ranges (A:A,B:B,C:C) I just create a list inside the document for translation.
And so on for as many columns as needed. Name this list ‘FINDCOL’.
Now in the formula I use the VLOOKUP to get a MATCH, and pull the result as an INDIRECT to make it work, like this:
(In this example I use A1 for my criteria, and A:A as my find range, and I need the sum of the values in the column which has “Result” as a header name in row 1:1)