Using a Variable inside of a SumIf equation (1 Viewer)

[kengooch]

I have a Named Range of values that I need to lookup a value in as my SumIf identifier for the total. However the Table can have additions and deletions and then be resorted so that the direct reference becomes a different value. I am summing three different values to get a composit total. Here is the equation that works, IF I don't change the order of items in the named range.

=SUMIF(tLUEvnts,Key!A13,tMins) + SUMIF(tLUEvnts,Key!A14,tMins)+SUMIF(tLUEvnts,Key!A25,tMins)


The issue I face is that the value Key!A13 in the first Sumif and the value Key!A14 and Key!A25 can change position based on additions deletions and resorting in the Named Range. I can actually generate the Location Reference with the following three equations

ADDRESS(MATCH((VLOOKUP((MID(E66,(FIND("Huddle",E66)),6)),tEvntsTm,1)),tEvnts,0)+3,1) yields the value $A$13
ADDRESS(MATCH((VLOOKUP((LEFT(E66,7)),tEvntsTm,1)),tEvnts,0)+3,1) yields the value $A$14
ADDRESS(MATCH((VLOOKUP(MID(E66,FIND(",",E66)+2,3),tEvntsTm,1)),tEvnts,0)+3,1) yields the value $A$25


The problem is, if I paste those values inline over the A13, the A14 and the A25, I get an error. I have tried using & "" and other means of getting the primary SumIF equation to accept the fixed Named range Value "Key!" and then combine it with the variable value ADDRESS(MATCH((VLOOKUP((MID(E66,(FIND("Huddle",E66)),6)),tEvntsTm,1)),tEvnts,0)+3,1)

Is it possible to marry these two values so that Excel reads them as a literal cell reference $A$13 so that my SumIf function will work?
Thanks so much in advance for any help with this!

 

[arnelgp]

Is it possible to marry these two values

in other States that is possible even when they both have Tails.
why don't you put your Formula in a Cell and use Indirect() function, pointing to that Cell.

 

[Minty]

Not really answering your question, but it's a bit weird using a match and a VLookup in conjunction.
The more usual method is to use Index and Match as per https://exceljet.net/index-and-match

 

[The_Doc_Man]

in other States that is possible even when they both have Tails.

Be careful, Arnel... your sense of humor is showing. (Not that I disapprove, you understand.)

 

[arnelgp]

Be careful, Arnel... your sense of humor is showing

well they are having parades with lots of candy.