Indirect and offset

[Tim]

I can't figure out how to make the "A4" change to "A5" when I copy the
formula to the next row.

=SUM(INDIRECT("A4:A"&(B4-C4)))

My "B" column is probably redundant, it's just a counter for what row
I'm on. (It actually should be one more since I need a header row).
What I'm trying to do is Sum A4:A1 if C4 =4, (four rows), or if C4=1,
I want the sum of A4:A3.

Col A Col B(just a row counter) Col C Col D (sum I need)
2 1 1
4 2 0
6 3 2
9 4 1

I suppose column B can be omitted, and just use an offset from C ?

If the example above is legible, I would want the formula in D3 to be
the sum of A3:A1. (I need to offset A3 up two rows.)

Thanks, Tim

 

[Dave Peterson]

=SUM(INDIRECT("A"&row()&":A"&(B4-C4)))

I can't figure out how to make the "A4" change to "A5" when I copy the
formula to the next row.

=SUM(INDIRECT("A4:A"&(B4-C4)))

My "B" column is probably redundant, it's just a counter for what row
I'm on. (It actually should be one more since I need a header row).
What I'm trying to do is Sum A4:A1 if C4 =4, (four rows), or if C4=1,
I want the sum of A4:A3.

Col A Col B(just a row counter) Col C Col D (sum I need)
2 1 1
4 2 0
6 3 2
9 4 1

I suppose column B can be omitted, and just use an offset from C ?

If the example above is legible, I would want the formula in D3 to be
the sum of A3:A1. (I need to offset A3 up two rows.)

Thanks, Tim

 

[JLGWhiz]

The answer I get is 15 base on the example posted. The reason A4 will not
change is because it is a string in quotation marks and not a cell address
string.

The cells your formula is adding is A4 and A3. B4 = C4 is 4 - 1 = 3
That make your formula read Sum(Indirect("A4:A3")
Since you only have integers in column A, the Indirect is superfluous here.
You could just as well use =a4 + a3.

Maybe you need to look at the help file for the Indirect fundcion again to
understand how it works.

 

[Tim]

Cool. Thanks Dave!

 

[Tim]

The answer I get is 15 base on the example posted.  The reason A4 will not
change is because it is a string in quotation marks and not a cell address
string.

The cells your formula is adding is A4 and A3.  B4 = C4 is 4 - 1 = 3
That make your formula readSum(Indirect("A4:A3")
Since you only have integers in column A, the Indirect is superfluous here.  
You could just as well use =a4 + a3.

Maybe you need to look at the help file for the Indirect fundcion again to
understand how it works.

Yea, I was trying to understand the examples in Indirect. Then I had
to find out what superfluous meant. Thanks anyway.