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