Search code examples
arraysexcelformulaoffset

Optimize =somproduct(---()) for combinations in excel formula

I'm looking to combine values in a lookup table using a list of combinations :

Combinations:               Expected result:    
#1  #2  #3  #4  #5  #6      A       B
1                           =2      =6
1   2                       =2+3    =6+7
1   3   5                   …   
1   4   5   6               …   
2   1   3   5   6           =3+2+5+2+1  …
…                               

Lookup table                                
    A   B                       
1   2   6                       
2   3   7                       
3   5   5                       
4   4   8                       
5   2   20                      
6   1   12

What I'm doing is :

enter image description here

Using this formula :

=SOMMEPROD(--($B$13:$B$18=$C4),$C$13:$C$18)
+SOMMEPROD(--($B$13:$B$18=$D4),$C$13:$C$18)
+SOMMEPROD(--($B$13:$B$18=$E4),$C$13:$C$18)
+SOMMEPROD(--($B$13:$B$18=$F4),$C$13:$C$18)
+SOMMEPROD(--($B$13:$B$18=$G4),$C$13:$C$18)
+SOMMEPROD(--($B$13:$B$18=$B4),$C$13:$C$18)

I'm looking to do something with either a somproduct(offset()) or an array formula that would do it in one line.

Can post my many failed attemps but it will cloud readability.

Solution

  • What might be easiest is to add helper columns where you calculate the running sums of A and B. Then you could do a VLOOKUP to retrieve the sum of interest.

    Another approach is something like:

    =SUM(OFFSET($C$13,0,0,N,1))

    where N is how many A items you want to sum. Likewise

    =SUM(OFFSET($D$13,0,0,N,1))

    sums the first N items of B.

    Hope that helps.