variable as a cell reference?

[jim sturtz]

i have this formula (and some others like it) that are basically summing
from the 24th row to the bottom row of a list of items, which in this case
is row 203.

=+SUMIF($A$24:$A$203,A5,$L$24:$L$203)

i continually add items into the list so i am continually having to do
'insert row' into the spreadsheet so as to maintain the pointer to the last
row, ie 203, which then becomes 204, 205...221.

is it possible to make the $A$203 be $A$BottomRow where BottomRow can then
be changed to whatever i want. i guess i would define a cell name to be
BottomRow and then fill in whatever value i need. would avoid having to do
all those inserts. Or perhaps more simply i could put a special character
value lets say 'bottom' and have excel scan column A from top to 99999
looking for the word 'bottom' and then using that row to be the value of
BottomRow.

hope this is clear.

thanks.

jim

 

[Dave Peterson]

Why not just use a giant number:

=SUMIF($A$24:$A$65536,A5,$L$24:$L$65536)

I used the maximum number of rows in a worksheet, but you could just make it
bigger than anything you ever expect.

(I also dropped that + from =+sumif(). It isn't required.

 

[Bob Phillips]

You could do

=SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L20:L"&BottomRow))

or if there is nothing below, and you don't have lots of these formulae, you
could just use

=SUMIF($A$24:$A$65536,A5,$L$24:$L$65536)
--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Harlan Grove]

You could do

=SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L20:L"&BottomRow))

....

INDIRECT is volatile. INDEX isn't (most of the time).

=SUMIF(A$24:INDEX(A:A,BottomRow),A5,L$24:INDEX(L:L,BottomRow))

 

[jim sturtz]

i guess 1) to see if it could be done 2) aint elegant 3) i might put
something below so this seemed like a good solution

thanks for all suggestions.

jim

 

[jim sturtz]

hmm. wasnt sure if we were supposed to be 'searching' for BottomRow to find
the 'marker' for it. or if i was supposed to define a variable called
BottomRow and fill in the value myself.
so i did both, singly, then together. i put BottomRow in a205 and i named
a1 to be BottomRow and put a 205 in it.

my prior formula went down to row 205, and the value totaled at that time
$208.25
=+SUMIF($A$24:$A$205,A5,$L$24:$L$205)

tried the function as
=SUMIF($A$24:INDEX(A:A,BottomRow),A5,$L$24:INDEX(L:L,BottomRow)) and it adds
up to $208.25, which is correct.


im mystified, to determine what was picking up row 205 to be the end of
everything i tried removing the BottomRow designator in A205. and cell h5
(which is the accumulator) still added to 208.25. so says i then the value
of 205 i put in a1 is the determinant, so i changed it to 100. and it did
change to 185.70, then i put in 0. the total went back to 208.25. i put in
blank, still 208.25. anyway, the short story is if i put a value bigger
than 24 into a1 and the row has the correct A5 value then it adds it
in...proceeding down the column till we get to the last entry at 205, but if
i put just nothing in a1, it sort of just adds up the whole column all the
way to the bottom. if i jump down to row 233, put the right a5 value in and
go out to L233 and put in an amount it adds that in too. so the value in
a1(bottomrow) is some sort of limiter.

if i only put 205, it sums to row205 (or anything less than that up to 24 at
which point it treats anything less than 24 as 24 and gives me the value in
row 24), if i put a blank or 0 in then it sums the whole column to end of
worksheet i guess. if i remove the NAME bottomrow then it errors.

jim