Proper syntax for summing over intersection of named ranges

[JenniferMurphy]

In the table below, the following named ranges are defined:
[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD]$A:$A[/TD]
[TD]TableLeft[/TD]
[/TR]
[TR]
[TD]$E:$E[/TD]
[TD]TableRight[/TD]
[/TR]
[TR]
[TD]$1:$1[/TD]
[TD]Jan[/TD]
[/TR]
[TR]
[TD]$2:$2[/TD]
[TD]Feb[/TD]
[/TR]
[TR]
[TD]$3:$3[/TD]
[TD]Mar[/TD]
[/TR]
</tbody>[/TABLE]

I need a formula in E1 that will be the equivalent of =sum(B1:D1) but using the named ranges.

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]Jan[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]Feb[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]17[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]Mar[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

I've tried as many combinations of row(), column(), address(), cell(), etc., as I can think of, but mostly I get errors.

I know I can accomplish this sum by naming B1:D1 something like "JanRow" and then using "=sum(JanRow)", but I like having the column named refer to the columns just to the left and right of the table so that they move if I add columns. But regardless of alternative solutions, I'd like to know how to do this.

 

[JenniferMurphy]

In particular I don't much like the named ranges for the rows (Jan, Feb etc) since your total formula for each row would need to be entered separately.

If I need to total the columns, I'd add headers and define named ranges for them, too.

/QUOTE]In addition, I think that you can achieve what you want (that is, have your sum work even if you insert columns just after the first column or just before the total column) without any named ranges or using any of the volatile functions like OFFSET or INDIRECT.

This formula can be just copied down.

	A	B	C	D	E
Jan
Feb
Mar

<tbody>
[TD="align: center"]1
[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"]7[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"]14
[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]6[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]17[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"]8[/TD]

</tbody>

Self-Adjusting Sum​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]E1[/TH]
[TD="align: left"]=SUM(INDEX(1:1,COLUMN(A1)+1):INDEX(1:1,COLUMN()-1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[/QUOTE]

I'm sure there is value in your suggestion, but for me, it's too literal. I like to use names whenever possible and it's almost always possible. It makes the formulas easier to read and understand months or years from now when I will have no idea what I was doing today.

 

[Peter_SSs]

I like to use names whenever possible and it's almost always possible.

Fair enough.
Do you like to avoid volatile functions where possible?
If so, the same non-volatile formula I posted above can be written with your named ranges.

=SUM(INDEX(Jan,COLUMN(TableLeft)+1):INDEX(Jan,COLUMN(TableRight)-1))

 

[Marcelo Branco]

Another formula for you to consider (draggable)
Note that the ranges Jan, Feb, Mar are not used (they are unnecessary)

=SUM(INDEX(TableLeft:TableRight,ROWS(A$2:A2),2):INDEX(TableLeft:TableRight,ROWS(A$2:A2),COLUMNS(TableLeft:TableRight)-1))

M.

 

[JenniferMurphy]

Fair enough.
Do you like to avoid volatile functions where possible?
If so, the same non-volatile formula I posted above can be written with your named ranges.

=SUM(INDEX(Jan,COLUMN(TableLeft)+1):INDEX(Jan,COLUMN(TableRight)-1))

Frankly, Peter, I don't fully understand all of the implications of volatile vs non-volatile functions. I know it has to do with when they are recalculated, but I've never paid much attention to it and,as far as I know, I haven't gotten bitten too badly -- or maybe I just wasn't aware of the bite.

I usually go for simpler. I am not a heavy or advanced user. It's just a tool for me.

PS: I do try to avoid volatile people, however.

 

[Marcelo Branco]

Well, it seems that my suggestions have not been very successful.
No problem, maybe next time.
Bedtime. Bye...

M.

 

[JenniferMurphy]

Well, it seems that my suggestions have not been very successful.
No problem, maybe next time.
Bedtime. Bye...

M.

On the contrary, I learn something from almost every post. So thanks for contributing. Some suggestions are are beyond my skill level.

Sweet dreams...