## Use SUMIF on a column defined by MATCH

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.

SUMIF Columns | |

1 | A:A |

2 | B:B |

3 | C:C |

4 | D:D |

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)

=SUMIF(A:A;A1;INDIRECT(VLOOKUP(MATCH("Result";1:1;0);FINDCOL;2;0)))