I have a little spreadsheet I'm creating to track my progress in a game. There are quest chains in the game that earn you points in an event. These are additive when trying to determine your total score in the event. To make data entry cleaner, I have a section with columns of the various levels for each quest chain. They look like the first five columns here:

All of this is on a single sheet - while I understand it may be conventional to put separate calculations on a separate page, I like the convenience of having them all on one sheet so that I can see them all at the same time. That's why I don't think the solution provided in

Explanation

• {"Level 1","Level 2","Level 3","Level 4"} is an array having the values of the dropdown in the order that corresponds to the columms from left to right.
• MATCH is a function that finds the position of the value selected in the dropdown, F8, in the above array.
• OFFSET grabs the cells from Column B to the right based on the number returned by MATCH
• SUM sums the values of the cells grabed by OFFSET.

Copy the formula from G8 to G2:G9.

To adapt this to your sheet, add the following formula to D2:

=SUM(OFFSET(I2:L2, 0, 0, 1, MATCH(C2,{"Level 1","Level 2","Level 3","Level 4"})))

then fill down.

NOTES:

If your spreadsheet uses , (commas) as the decimal separator, then replace the , with ; (semicolons).

If your spreadsheet becomes complex, i.e. you sheet becomes very large or you add many sheets and many formulas, then you might require another solution. If that is the case, we will require more details.

References

• 

  and the whole column in one go:

  =BYROW(F2:INDEX(F:F, MAX(ROW(F:F)*(F:F<>""))), 
   LAMBDA(x, IFNA(SUM(FILTER(OFFSET(x,,-4,,4), B1:E1<=x)))))
  

  

  F2:INDEX(F:F, MAX(ROW(F:F)*(F:F<>""))) translates to F2:F8 based on: https://stackoverflow.com/a/74281216/5632629

  LAMBDA usage explanation can be found here: https://stackoverflow.com/a/74393500/5632629 in "WHY LAMBDA ?" section