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


"Harlan Grove" wrote in message
...
"Bob Phillips" wrote...
You could do

=SUMIF(INDIRECT("A24:A"&BottomRow),A5,INDIRECT("L 20: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))