Table:
1) Vertical axis is Field Names, sorted alphabetically in ascending order from the top.
2) Horizontal axis is Tiers, ascending from left to right. The maximum Tier range is 0-101, though some Fields have as few as 2 tiers.
3) Data within the table is cumulative costs associated with each Tier, specific to each Field Name.

User Input:
1) Pull down menu to select the field name. (FieldName)
2) Target Tier. (TargetTier)
3) Current (or starting) Tier. This may not always be Tier 1. (StartTier)

Goal:
I need the sum of the target cells in the range: FieldName(StartTier:TargetTier), and I need to do it entirely via user input. The purpose is to determine the remaining cost of attaining a specific Tier of a specific Field. Because the costs are cumulative, each Tier before the Target needs to be added in, unless it's already been attained. Tiers may only be attained in numerical order. It is literally a progressive level system, and each Field has a different progression rate.

Current State:
I use two SUMIF functions. One returns the total value of all the Tiers in a given Field up to and including the Target, while the other returns the total value of all the same Tiers up to the Start. After that, a third cell applies some subtraction. The problem with this set up is I have to manually change the cell references within the SUMIF formulas. This quickly becomes tedious because of all the Fields available.

The pull down menu is also functional, and I can VLOOKUP with it.

The Question:
Is there a way to make the SUMIF function accept a target row designation without editing the formula directly?


I don't mind having two SUMIF functions to set up my math. In fact, I'll probably have those to display the process step-by-step. I like visual data, and it'll allow me to see if there are any mess ups anywhere.

If this is confusing, I'll upload the file.

NOTE: It strikes me that Access might be better for this. However, I lack Access. I also don't have any knowledge of VB programming.