SUM using string tag as start point

Data looks like this:

DATE AMOUNT
23-Sep-06 $1000.00
29-sep-06 $1500.00
06-Oct-06 BASE
12-Oct-06 $1200.00
16-Oct-06 $1425.00
. . .

I want to sum all the values in the AMOUNT column from the cell following the "BASE" entry to the end of the column.

My initial attempt: SUM(IF($A:$A > LOOKUP("BASE",$B:$B,$A:$A),$B:$B ))
The thought being to find the date of the "BASE" entry and sum all of the values with a date greater than that.

I need to be able to move the "BASE" entry around and have the sum automatically work.
Also I need to be able to add rows of data and have the formula automatically work.

Any ideas greatly appreciated.
-chaz

Originally Posted by chaz

Data looks like this:

DATE AMOUNT
23-Sep-06 $1000.00
29-sep-06 $1500.00
06-Oct-06 BASE
12-Oct-06 $1200.00
16-Oct-06 $1425.00
. . .

I want to sum all the values in the AMOUNT column from the cell following the "BASE" entry to the end of the column.

My initial attempt: SUM(IF($A:$A > LOOKUP("BASE",$B:$B,$A:$A),$B:$B ))
The thought being to find the date of the "BASE" entry and sum all of the values with a date greater than that.

I need to be able to move the "BASE" entry around and have the sum automatically work.

Any ideas greatly appreciated.
-chaz

=SUM(INDIRECT("B"&MATCH("base",B2:B6)+1&":B6"))

Amended to

=IF(ISERROR(MATCH("base",B2:B6)),SUM(B2:B6),SUM(INDIRECT("B"&MATCH("base",B2:B6)+1&":B6")))

--

Originally Posted by chaz

Bryan,
Wouldn't using references like B2:B6 make the formula break when new rows of data are added?
-chaz

Of course, but the response was to your request, which indicated a header row and five rows of data, thus the formula, a guide as to 'how-to', is written to match yor example.

Adjust the range of the formula to suit your needs.

---

Originally Posted by chaz

Bryan,
I appologize for not being clearer.
the ". . ." at the end of the data was meant to indicate that the data could continue and grow.
I've tried using column references (B:B) but it does not work.
Is there a way to tell excel "the last row" ?
Or a way to write a furmula so that continuing to the last row is implied?
-chaz

If column B has no blanks, then

=IF(ISERROR(MATCH("base",INDIRECT("B2:B"&COUNTA(B:B)))),SUM(INDIRECT("B2:B"&COUNTA(B:B))),SUM(INDIRECT("B"&MATCH("base",INDIRECT("B2:B"&COUNTA(B:B)))+1&":B"&COUNTA(B:B))))

should work.

If column B has blanks, but another column doesn't, then base the CountA on that column

---

I guess that's one straight from the 'weird formulae' book

Originally Posted by chaz

Bryan,
SOLVED!
Thank You!
It took a while to understand what you were doing and then I had to tweek things a bit but I now understand how you are building the reference addresses.
I had thought that the ROW function would somehow be involved but this works. Coming from a systems/database background it seems a little weird that there are no meta symbols to communicate such things as "first", "last", etc.
I'm sure I'll get the hang of it though.
Thanks for hanging in there with me.
-chaz

Chaz,

Good to see that it worked for you, and thanks for the response.

---