Is it possible to create a formula that would sum multiple columns by looking up two parameters?-CodePudding

The question may sound very odd, so let me explain.

E.g. I have a table that looks like this:

       1    2    3    3    
Soda   1    2    1    0    
Beer   2    0    0    0    
Beer   2    1    2    2      
Soda   0    2    0    2

I would like to sum each "drink number" for every category, like this:

      1  2  3
Soda  1  4  3
Beer  4  1  4

I have used the IFS and SUMIF formula sequence for this (where B$1 = "Column number",$A2 = "Drink name", rest is just columns corresponding to value locations):

=IFS(B$1=“1”,SUMIF($A$2:$A$5,$A2,$B$2:$B$5),B$1=“2”,SUMIF($A$2:$A$5,$A2,$C$2:$C$5),B$1=“3”,sum(SUMIF($A$2:$A$5,$A2,$D$2:$D$5),SUMIF($A$2:$A$5,$A2,$E$2:$E$5)))

But it is very robust, and needs to be changed manually if the initial table is changed. I would like to make my formula adaptable to changes. Say, it "looks up" row and column values and sums up every column values by them. For example, if one more column "2" is added, and "Beer" is changed to "Milk" (but the "column length" remains the same);

       1    2    2    3    3    
Soda   1    2    1    1    0    
Milk   2    0    1    0    0    
Milk   2    1    0    2    2      
Soda   0    2    1    0    2

The formula automatically adjusts to this change, and calculate new sum:

      1  2  3
Soda  1  6  3
Milk  4  2  4

Is it possible to create such formula? Because I'm not even sure if something like this can be done with basic Excel formulas. Thank you beforehand.

CodePudding user response：

Yes. For example:

=SUMPRODUCT(($A$2:$A$5=$G2)*($B$1:$E$1=H$1),$B$2:$E$5)

where G2 has Soda and H1 has 1. Then copy across and down (assuming Beer in G3 and 2, 3 in I1:J1)