Offset, sum down to the first blank row

T

Tami

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.
 
L

Lars-Ã…ke Aspelin

Tami said:
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: (e-mail address removed) ---
 
T

Tami

Wonderful!...thank you...As i added lines at the end of the range, it picked
them up.
now, can you make the formula flexible if i insert a row at the beginning of
the range?....so the formula needs to know to always start the immediate row
beneath it. do we use offset?
 
T

Tami

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:)
 
L

Lars-Åke Aspelin

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
 
T

T. Valko

Try this array formula** :

=SUM(INDIRECT("B2:B"&MATCH(TRUE,INDIRECT("B2:B100")="",0)+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Adjust for a reasonable end of range B100.

Note that immediately after you insert a new row 2 cell B2 will be empty and
is therefore the first empty cell in the referenced range. So, the sum will
show 0 until you enter something in cell B2.
 
T

Tami

Thank you both...they both worked!
tami

Lars-Ã…ke Aspelin said:
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




.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

SUM & OFFSET 1
SUMIF with OFFSET 5
sum of multiple rows 2
Vlookup to reference offset 1
Sum if not blank 2
Row increments - 7
sum product if 2
YTD Calculation using SUM and OFFSET 4

Top