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.

 

[RoryA]

You could do this:

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

Although, since your first column appears to be text, you could just use:

=SUM(A1:INDIRECT("RC[-1]",0))

 

[JenniferMurphy]

You could do this:

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

That works, thanks. I kept trying to generate some form of "=SUM(B1:D1)" using the range names. 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.

 

[shg]

[Table="width:, class:grid"][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]Jan[/td][td]

3​

[/td][td]

7​

[/td][td]

4​

[/td][td]

14​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

2​

[/td][td]Feb[/td][td]

6​

[/td][td]

5​

[/td][td]

6​

[/td][td]

17​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

3​

[/td][td]Mar[/td][td]

4​

[/td][td]

1​

[/td][td]

3​

[/td][td]

8​

[/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

5​

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

28​

[/td][td]E5: =SUM(TableLeft:TableRight Jan)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

6​

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

34​

[/td][td]E6: =SUM(TableLeft:TableRight Feb)[/td][/tr]
[tr][td="bgcolor:#C0C0C0"]

7​

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

16​

[/td][td]E7: =SUM(TableLeft:TableRight Mar)[/td][/tr]
[/table]

It would be better (safer) if TableLeft referred to col B.

 

[JenniferMurphy]

[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]Jan[/TD]
[TD]

3​

[/TD]
[TD]

7​

[/TD]
[TD]

4​

[/TD]
[TD]

14​

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

2​

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

6​

[/TD]
[TD]

5​

[/TD]
[TD]

6​

[/TD]
[TD]

17​

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

3​

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

4​

[/TD]
[TD]

1​

[/TD]
[TD]

3​

[/TD]
[TD]

8​

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

4​

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

5​

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

28​

[/TD]
[TD]E5: =SUM(TableLeft:TableRight Jan)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

6​

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

34​

[/TD]
[TD]E6: =SUM(TableLeft:TableRight Feb)[/TD]
[/TR]
[TR]
[TD="bgcolor: #C0C0C0"]

7​

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

16​

[/TD]
[TD]E7: =SUM(TableLeft:TableRight Mar)[/TD]
[/TR]
</tbody>[/TABLE]


It would be better (safer) if TableLeft referred to col B.

Why is it safer?

Also, why do you recommend moving TableLeft to Col B, but not moving TableRight to Col D? Col E is the sum, not part of the table.

I used to do as you suggest. I switched to assigning the labels just outside the table so I could add columns anywhere in the table without screwing up the formulas. If TableLeft is assigned to the leftmost column of the actual table and I copy that column to the left, the new column will be outside the table (to the left of TableLeft). There is the opposite problem on the right. I tried to remember to always copy into the table, but I seemed to forget. This way, it works. But the formulas would be a lot simpler your way.

 

[Tetra201]

Withdrawn.

 

[shg]

Why is it safer?

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

Col E is the sum, not part of the table.

Missed 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="width:, class:grid"][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]
[/table]

 

[Tetra201]

... I kept trying to generate some form of "=SUM(B1:D1)" using the range names...

Maybe

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

 

[Marcelo Branco]

That works, thanks. I kept trying to generate some form of "=SUM(B1:D1)" using the range names.

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

Assumes col 1 as text

M.

 

[Marcelo Branco]

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.