How do I put a variable column number in the sum() function?
 

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7 determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

Hi,

The following formula should do the trick

=SUM(OFFSET(F7,,,,A1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BobC" wrote:

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7 determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

I also had trouble finding the button ... so I will type it ...
*YES*
Thanks!
Bob

Shane Devenshire wrote:
Hi,

The following formula should do the trick

=SUM(OFFSET(F7,,,,A1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BobC" wrote:

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7 determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

Here's a non-volatile method:

=SUM(F7:INDEX(F7:IV7,A1))

If A1 is empty the formula will calculate the entire range.

--
Biff
Microsoft Excel MVP


"BobC" wrote in message
...
I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in row
7 (form F to I).
I would like to be able to have the number of cells in row 7 determined by
a value in a specific cell such as cell A1 (in this example would have
contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

Hi,

The button is over on the right and has the comment Was this post helpful to
you?

But you wouldn't be the first person to miss it, and it may not even display
that way in some browsers.

Cheers,
Shane

"BobC" wrote:

I also had trouble finding the button ... so I will type it ...
*YES*
Thanks!
Bob

Shane Devenshire wrote:
Hi,

The following formula should do the trick

=SUM(OFFSET(F7,,,,A1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BobC" wrote:

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7 determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

Bob is probably not using Microsoft's web interface to the newsgroup, so if
he is accessing the newsgroup directly (as many of us do) he wouldn't see
the button.
--
David Biddulph

"Shane Devenshire" wrote in
message ...
Hi,

The button is over on the right and has the comment Was this post helpful
to
you?

But you wouldn't be the first person to miss it, and it may not even
display
that way in some browsers.

Cheers,
Shane

"BobC" wrote:

I also had trouble finding the button ... so I will type it ...
*YES*
Thanks!
Bob

Shane Devenshire wrote:
Hi,

The following formula should do the trick

=SUM(OFFSET(F7,,,,A1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BobC" wrote:

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7
determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?

How do I put a variable column number in the sum() function?
 

Bob is using Mozilla


Gord

On Sun, 23 Nov 2008 09:37:54 -0000, "David Biddulph" <groups [at]
biddulph.org.uk wrote:

Bob is probably not using Microsoft's web interface to the newsgroup, so if
he is accessing the newsgroup directly (as many of us do) he wouldn't see
the button.

How do I put a variable column number in the sum() function?
 

Thanks!!!
I appreciate the help!
Everything that I was trying seemed to yield syntax errors!

Bob

T. Valko wrote:
Here's a non-volatile method:

=SUM(F7:INDEX(F7:IV7,A1))

If A1 is empty the formula will calculate the entire range.

How do I put a variable column number in the sum() function?
 

Shane,

No buttons shows.
I thought you were just being funny.
I'm using Mozilla (SeaMonkey).
You'll have to press the button in my behalf.
Thanks again! ... it worked!

Bob

Shane Devenshire wrote:
Hi,

The button is over on the right and has the comment Was this post helpful to
you?

But you wouldn't be the first person to miss it, and it may not even display
that way in some browsers.

Cheers,
Shane

"BobC" wrote:

I also had trouble finding the button ... so I will type it ...
*YES*
Thanks!
Bob

Shane Devenshire wrote:
Hi,

The following formula should do the trick

=SUM(OFFSET(F7,,,,A1))

If this helps, please click the Yes button

Cheers,
Shane Devenshire

"BobC" wrote:

I want to sum cells based on a number contained in cell A1
For example:
I currently have the formula =SUM(F7:I7) which sums 4 of the cells in
row 7 (form F to I).
I would like to be able to have the number of cells in row 7 determined
by a value in a specific cell such as cell A1 (in this example would
have contained the number 4).

I do not seem to be able to functions of any kind in the range sum
expression?