Row() and SUM() ?s

[Tom]

I'd like to have the ability to use the SUM() and other functions by
selecting a set # of rows which is determined by the number placed in the
FIRST row of a worksheet. For example, if I wanted to SUM for 3 rows I would
normally do SUM(A4:A6), SUM(A5:A7), SUM(A6:A8).... or generally
SUM(A((Row())-(A1-1):A(Row())) where A(Row()) is the current ROW # and (A1-1)
is one less than the value in A1 (# or rows desired to add or apply the
function to).
How can I do this for say A1=3, B1=5, C1=7 or even just varying what's in
A1...G1 to see how things work? I'd like to NOT have a specific summation
function. That is, NOT using SUM(A2:A10).
TIA

 

[Jacob Skaria]

If I understand correctly the below would help

In A1 enter 5
In B1 enter 10

The below formula would sum A5:A10
=SUM(INDIRECT("A" & A1 & ":A" & B1))


In A1 enter A5
In B1 enter A10

The below formula would sum A5:A10
=SUM(INDIRECT( A1 & ":" & B1))


If this post helps click Yes

 

[Tom]

Jacob,
You're extremely close but let me clarify with further illustrations. I was
unclear in what I wrote.

I need the value in B1, C1, D1.... G1 to be the # of rows I want to SUM (or
whatever function I'm using). That is, if B1 contains 3 and C1 contains 5
and D1 contains 10 I want to be able to use the SUM function for the values
in Column A for B1=3 for column B's SUM functions (3 rows); C1=5 for column
C's SUM functions (5 rows); D1=10 for column D's SUM functions (10 rows).
A B C D
row 1 3 5 10
row 2 10
row 3 20
row 4 30 SUM(A2:A4)
row 5 40 Sum(A3:A5)
row 6 50 Sum(A4:A6) Sum(A2:A6)
row 7 60 sum(A5:A7) Sum(A3:A7)
row 8 70 Sum(A6:A8) SUM(A4:A8)
row 9 80 Sum(A7:A9) Sum(A5:A9)
row 10 90 Sum(A8:A10) Sum(A6:A10) ...
The above SUM() functions are the normal ways of doing something. I want
the SUM function to REFERENCE the B1 (or C1, D1...) value with calculations
to determine the appropriate start/end for SUM(A???:A????).
Thanks!

 

[Jacob Skaria]

In cell B2 enter the below formula
=IF(B$1+1<=ROW(),SUM(INDIRECT("A" & ROW()-B$1+1 & ":A" & ROW())),"")

Copy that across to C2,D2 and then copy down as required

If this post helps click Yes

 

[Tom]

Jacob,
I used your example and it's EXTREMELY close to what I need. I currently
have a #REF error on this:
=IF(D$1+1<=ROW(),SUM(INDIRECT("RawData!F" & ROW()-D$1+1&":RawData!F" &
ROW())),"")
Everything works fine (using the Evaluate capabilities) until I get to
(based on D$1+1 being TRUE):
INDIRECT(SUM("RawData!F18:RawData!F21"))
Note: This is part of this:
IF(TRUE,INDIRECT(SUM("RawDat!F18:RawData!F21")),"")
It certainly appears that the INDIRECT function is erring out here.

What do you suggest?
THANKS!!!

 

[Tom]

I've tried several ideas... none of them work to resolve the #REF or #VALUE
errors.
SOME ideas I've tried: Format to be General, Format to be Number (0 decimal
places), Format to be Text for the D$1 cell. The format on the RawData!
cells are numeric.
In stepping through the calculations, everything appears to work fine except
for the final calculation on
"IF(TRUE,SUM(INDIRECT("RawData!F2:RawData!F5")),"). Once THIS calculation is
performed I receive the #REF error message. I receive
IF(TRUE,(SUM(#REF!),""). So, the issue is evidently relating to the RawData
cells and/or the format of the cell types or ????.
Thanks!

ANY assistance would be appreciated. The format for the cells to store the
SUM calculated values are numeric.
I'm totally lost! ;-(

 

[Mtavolieri]

How do you use the formula =SUM(a((ROW())-(A1-1):A(ROW())) ? I keep getting
an error message that my formula is incorrect.

 

[David Biddulph]

You would need to use the INDIRECT function.

 

[Mtavolieri]

I want to write the formula VLOOKUP(3.119,M7:M (ROW()) , True)
So that the range the look up function is using is between a set point, M7,
and the row the formula is. But I cant get Microsoft Excel to understand that
I want the value in the brackets after the M to be the row value. Any
suggestions?

 

[David Biddulph]

Yes. Use the INDIRECT function.