Thank you both...they both worked!
tami

"Lars-Åke Aspelin" wrote:

With some changes the case when you insert new a new row between rows
1 and 2 can be handled. Try this:

=SUM(OFFSET(B1,1,,MATCH(TRUE,ISBLANK(OFFSET(B1,1,, 100)),0)))

Still an array formula.

Hope this helps / Lars-Åke


On Sat, 26 Dec 2009 09:39:01 -0800, Tami
wrote:

ok, i'll try it. By chance, will it address my reply to teethless mama "what
happens when i insert a row at B2?", will your forumula pick it up?

thanks:-)

"Lars-Åke Aspelin" wrote:


"Tami" wrote in message
...
can anyone write the formula to sum down to the first blank row it
encounters?
In the sample data below I want the xx to sum only to 60
Assume the word sum is in cell A1 and the xx is in cell B1.

sum xx
Blue 10
Black 20
Red 30

Red 20
black 40

thanks much.

p.s. would this formula be considered "volatile" and therefore
"risky"....if
so, what's the risk. thx.

Try this formula in cell B1:

=SUM(OFFSET(B2,,,MATCH(TRUE,ISBLANK(B2:B100),0),))

Note: This is an array formula that has to be confirmed by CTRL+SHIFT+ENTER
rather than just ENTER.

Hope this helps / Lars-Åke


--- news://freenews.netfront.net/ - complaints: ---
.


.