Sumif entire table with multiple row and column criteria

[alimat]

Hi! I'm attempting to sum all values in a table that that 1. match a specific value in a header (column) and 2. match multiple values in the rows (there are about 20 values). Each of the values I want the data to match are referencing cells that contain the value.

The below formula I pasted below works, it's just cumbersome. I think there may be a way to do this with SUMPRODUCT, but I haven't been able to figure it out. Can anyone help?

Also, if there is a better formula for summing things from a 2 dimensional table, with 2 dimensional criteria, I'd welcome the advice!

*the formula below is an array**


=(SUMIF(Table6[[#All],[Row Labels]],$A$169,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$170,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$171,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A172,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$173,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A174,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$175,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$176,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$177,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$178,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$179,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0)))+SUMIF(Table6[[#All],[Row Labels]],$A$180,INDEX(Table6[#All],0,MATCH(K$167,Table6[#Headers],0))))

 

[phoenixdown]

Try using SUMIFS

=sumifs(Column to SUM Range, Criteria Range 1, Criteria 1, Criteria Range 2, Ciriteria 2, etc)

 

[alimat]

This works if you are defining the column to sum. I need to define a range for excel to find the one column and then add from it.

 

[Sinon]

Please provide a sample of your workbook as it will be easier to understand the formula and suggest an alternative.