Using Sum function dynamically
 

I am trying to figure out how to adjust the range of cells which are summed based on the value within another cell.

so if I had =sum(a1:f1) but I wanted to have the start and end points be dynamic from a column perspective (the row would remain constant). So based on the value of cell a10 and b10, for instance, the starting and ending column position might change to be B1 and E1 or some subset or expansion of A1:F1.

From some of what I have found I think I use INDIRECT but the examples don't reflect what I am trying to do exactly.

Thanks for any help

David

Using Sum function dynamically
 

Hi David,

Am Fri, 3 Nov 2017 10:31:50 -0700 (PDT) schrieb
:

I am trying to figure out how to adjust the range of cells which are summed based on the value within another cell.

so if I had =sum(a1:f1) but I wanted to have the start and end points be dynamic from a column perspective (the row would remain constant). So based on the value of cell a10 and b10, for instance, the starting and ending column position might change to be B1 and E1 or some subset or expansion of A1:F1.

what values are in A10 and B10?

If there is e.g. B and G then use:
=SUM(INDIRECT(A10&"1:"&B10&"1"))

If you want to copy down the formula you better try:
=SUM(INDIRECT($A$10&ROW(A1)&":"&$B$10&ROW(A1)))


Regards
Claus B.
--
Windows10
Office 2016

Using Sum function dynamically
 

Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position

Using Sum function dynamically
 

Hi,

Am Fri, 3 Nov 2017 11:33:22 -0700 (PDT) schrieb
:

Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position

then try:
=SUM(OFFSET($A1,,$A$10-1,,$B$10-$A$10+1))


Regards
Claus B.
--
Windows10
Office 2016

Using Sum function dynamically
 

On Friday, November 3, 2017 at 12:51:54 PM UTC-6, Claus Busch wrote:
Hi,

Am Fri, 3 Nov 2017 11:33:22 -0700 (PDT) schrieb


Thanks for this. What if the values in a10 and b10 were numeric?

As in a10 and b10!was the relative column position

then try:
=SUM(OFFSET($A1,,$A$10-1,,$B$10-$A$10+1))


Regards
Claus B.
--
Windows10
Office 2016

great thanks that will work fine.