Search code examples
excelexcel-2010worksheet-function

Sum Column that matches a given criteria in Excel

How would I sum vertically the columns that meet a given criteria?

For example:

A B C D E F G
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7
1 2 3 4 5 6 7

If criteria = A, then the formula would give me 4 if B, 8 if C, 12. I would like the criteria input to be a reference-able cell.

Thanks for your help!

Solution

  • Use INDEX to return the correct array. Use MATCH to return the correct column:

    =SUM(INDEX(2:5,0,MATCH(J1,1:1,0)))

    ![enter image description here