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.

 

[Marcelo Branco]

A better version - works with either text or dates (numbers) in TableLeft

=SUM((Jan TableLeft) : (Jan TableRight))-(Jan TableRight) -N(Jan TableLeft)

M.

 

[JenniferMurphy]

Because if col A was a date, it would be added to the sum.

Not using Rory's solution. I really don't want to use any solution that depends on Excel excluding text from a sum. I find that error prone. I posted this question because I wanted a way to sum over the data only. Rory gave me exactly that.

If col A contains text, I would do that like this:

tbl refers to: =INDEX(Sheet1!$1:$1048576, ROW(Sheet1!$A$1) + 1, COLUMN(Sheet1!$A$1) + 1):INDEX(Sheet1!$1:$1048576, MATCH(conZzz, Sheet1!$A:$A), COLUMN(Sheet1!$E$1) - 1)

Select some cell in row 1 and define myRow refers to: =1:1 (no $ signs)

ConZzz refers to: =REPT("z", 255) (in all my workbooks)

That definition allows inserting columns left of B, left of E, above row 2, and at the bottom (and, of course, the interior). Then

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #C0C0C0"][/TD]
[TD="bgcolor: #C0C0C0"]

A​

[/TD]
[TD="bgcolor: #C0C0C0"]

B
​

[/TD]
[TD="bgcolor: #C0C0C0"]

C
​

[/TD]
[TD="bgcolor: #C0C0C0"]

D​

[/TD]
[TD="bgcolor: #C0C0C0"]

E​

[/TD]
[TD="bgcolor: #C0C0C0"]

F​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

1
​

[/TD]
[TD="bgcolor: #F3F3F3"]

Month​

[/TD]
[TD="bgcolor: #F3F3F3"]

Val1​

[/TD]
[TD="bgcolor: #F3F3F3"]

Val2
​

[/TD]
[TD="bgcolor: #F3F3F3"]

Val3​

[/TD]
[TD="bgcolor: #F3F3F3"]

Total​

[/TD]
[TD="bgcolor: #909090"][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

2
​

[/TD]
[TD]Jan[/TD]
[TD]

3​

[/TD]
[TD]

7
​

[/TD]
[TD]

4​

[/TD]
[TD="bgcolor: #E5E5E5"]

14​

[/TD]
[TD]E2: =SUM(tbl myRow)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

3
​

[/TD]
[TD]Feb[/TD]
[TD]

6​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD="bgcolor: #E5E5E5"]

17​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

4​

[/TD]
[TD]Mar[/TD]
[TD]

4​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD="bgcolor: #E5E5E5"]

8​

[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

5
​

[/TD]
[TD][/TD]
[TD="bgcolor: #E5E5E5"]

13
​

[/TD]
[TD="bgcolor: #E5E5E5"]

13
​

[/TD]
[TD="bgcolor: #E5E5E5"]

13​

[/TD]
[TD][/TD]
[TD]B5: =SUM(tbl myCol)[/TD]
[/TR]
</tbody>[/TABLE]

I'd have to study this to understand it, which I would do except I already have a solution that does exactly what I want and it does not extend the sum beyond the boundaries of the table.

But thanks for your effort.

 

[JenniferMurphy]

Maybe

=SUM(INDIRECT(CELL("address",OFFSET(TableLeft,0,1) Jan)&":"&CELL("address",OFFSET(TableRight,0,-1) Jan)))

Is this better is some way than Rory's solution?

=SUM(OFFSET(TableLeft,,1):OFFSET(TableRight,,-1) Jan)

This is simple, I understand it, and, best of all, it works!

 

[JenniferMurphy]

oops...

Disregard my previous post

Maybe...
=SUM((TableLeft Jan) : (TableRight Jan))-(Jan TableRight)

Assumes col 1 (TableLeft) as text.
If not try:
=SUM((TableLeft Jan) : (TableRight Jan))-(Jan TableRight)-(Jan TableLeft)

M.

Again,this appears to extend the sum beyond the boundaries of the table itself, which I would prefer not to do. Rory's solution works well and I understand it. But thanks.

PS: Are you related to Castelo Branco, who was president of Brazil back in the 60s?

 

[Peter_SSs]

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.
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.

Book1
	A	B	C	D	E
1	Jan	3	7	4	14
2	Feb	6	5	6	17
3	Mar	4	1	3	8
Self-Adjusting Sum

Cell Formulas
Range	Formula
E1	=SUM(INDEX(1:1,COLUMN(A1)+1):INDEX(1:1,COLUMN()-1))

 

[Tetra201]

Is this better is some way than Rory's solution?

No. I put it together in response to this:

...I think I tried some version of Offset, but when it returned a numeric, instead of an alpha, value for a column, I didn't think it would work.

The only practical use is that you can see what exactly is summed:

=CELL("address",OFFSET(TableLeft,0,1) Jan)&":"&CELL("address",OFFSET(TableRight,0,-1) Jan)

 

[Tetra201]

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...

Here is a "draggable" formula:

=SUM(OFFSET(TableLeft,,1):OFFSET(TableRight,,-1) INDIRECT(A1))

 

[Marcelo Branco]

Again,this appears to extend the sum beyond the boundaries of the table itself, which I would prefer not to do. Rory's solution works well and I understand it. But thanks.

I don't think so. The formula doesn't add any value out of the table boundaries. See post 11

PS: Are you related to Castelo Branco, who was president of Brazil back in the 60s?

Yes, but not close.

M.

 

[Peter_SSs]

Here is a "draggable" formula:

=SUM(OFFSET(TableLeft,,1):OFFSET(TableRight,,-1) INDIRECT(A1))

True, but includes yet another volatile function so not one I'd choose when there's a reasonably straight-forward non-volatile option.
.. and what a pain in the neck to set up all the named ranges!

 

[Tetra201]

... not one I'd choose when there's a reasonably straight-forward non-volatile option.

Same here.

... and what a pain in the neck to set up all the named ranges!

Concur.