SUM using string tag as start point

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.

---