Sum of cells in intersection of current column with a named range

[rhayward]

I have a range called MyRange.

I want to put a formula in the cells of a row, somewhere below MyRange that gives the SUM() of cells in MyRange that intersect with the column of the current cell.

Is there some clean way of doing this, using names, and maybe the COLUMN() function, instead of specific addresses in the formula?

Regards
Richard

 

[VBE313]

How to sum columns for a selected range using VBA?

After I have selected a range containing numerical values, I want to, via VBA, input a =SUM formula at the bottom of each column, i.e. on the row after the last selected row. For each column it sho...

stackoverflow.com

 

[Peter_SSs]

Try this, copied across.

20 03 18.xlsm
	B	C	D	E	F	G
1
2	6	6	7	6	0	1
3	3	5	5	9	0	3
4	5	8	6	2	2	4
5	9	4	3	1	1	-20
6
7
8	23	23	21	18	3	-12
Sum

Cell Formulas
Range	Formula
B8:G8	B8	=SUM(INDEX(MyRange,0,COLUMNS($B8:B8)))

Named Ranges
Name	Refers To	Cells
MyRange	=Sum!$B$2:$G$5	B8:G8

 

[Fluff]

Another option

+Fluff.xlsm
	B	C	D	E	F	G
1
2	6	6	7	6	0	1
3	3	5	5	9	0	3
4	5	8	6	2	2	4
5	9	4	3	1	1	-20
6
7
8	23	23	21	18	3	-12
Import

Cell Formulas
Range	Formula
B8:G8	B8	=SUM(MyRange INDEX(B:B,0))

 

[Peter_SSs]

=SUM(MyRange INDEX(B:B,0))

or just

=SUM(MyRange B:B)

 

[Fluff]

Over thinking things again

 

[rhayward]

or just

=SUM(MyRange B:B)

That's nice & simple.

Without involving myself in vba, there is no built in function that returns a range corresponding to the column of the cell in which the function is used?

 

[mikerickson]

You could use
=SUM(INDEX(myRange, 0, COLUMN()-COLUMN(myRange)+1))

to get your sum.

To return the column of the cell that holds the formula as a range, you could us =INDEX(1:1048576,0,COLUMN()), which will cause a circular reference error.

 

[rhayward]

Thanks for your help all.
Most useful.

Richard

 

[Peter_SSs]

Without involving myself in vba, there is no built in function that returns a range corresponding to the column of the cell in which the function is used?

Like this?

20 03 18.xlsm
	B	C	D	E	F	G
1
2	6	6	7	6	0	1
3	3	200	5	9	0	3
4	5	8	6	2	2	4
5	9	4	3	1	1	-20
6
7
8	23	218	21	18	3	-12
Sum

Cell Formulas
Range	Formula
B8:G8	B8	=SUM(INDEX(MyRange,0,MATCH(COLUMN(),COLUMN(MyRange))))

Named Ranges
Name	Refers To	Cells
MyRange	=Sum!$B$2:$G$5	B8:G8

BTW, I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version.