SUMPRODUCT Looking Up Values in a Range

[natekris8183]

I have a letter grading of A-E (otherwise 1-5). The range is a list of locations (e.g. 536 locations). I have a range (e.g. K9:K13) with values input (e.g. {"5", "4", "3", "2", "1"}). This example, the values correspond the A getting "5", B getting "4", and so forth. As I am going down the rows, I need to sum the values of everything less than to assure I do not go beyond the allotted quantity (e.g. if the row I am in a "B" store, I need the sum the values of all A stores, which get 5). I am create an array of cells using the following. The offset just merely allows me to only look at the relevant range with data, since this is a dynamic list that changes the displayed stores with pivot table splitters on the page.

=SUMPRODUCT(INDIRECT(ADDRESS(((((((CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))>64)*(CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))<=CODE($E22)))*CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1)))-64)>0)*(((((CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))>64)*(CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1))<=CODE($E22)))*CODE(OFFSET(TemplateSalesVolumeGroupDataRng,,,SUMPRODUCT(IFERROR(LEN(TemplateSalesVolumeGroupDataRng)>0,0)*1),1)))-58))),COLUMN($K:$K),1),TRUE))

The result gives me something such as:

=SUMPRODUCT(INDIRECT({"$K$9","$K$10","$K$0","$K$9",...},TRUE))

I get a #VALUE! for the return because it assumes the array are numeric values. In this case, the indirect function isn't looking to the cell value inside the SUMPRODUCT array. Any thoughts?