Sum until hit blank
 

hi.
I need a formula that automatically sums down to the first blank cell.
In the data below i need a formula for the Total lines below...sometimes
there are 3 styles , sometimes 2 styles, sometimes 1 style. But i don't want
to have the user to have to adjust he formula every time as it will add risk
for human error.
any suggestions?

Total 450
Style A 100
Style B 200
Style C 150

Total 200
Style D 150
Style E 50

Total 250
Style F 250

Sum until hit blank
 

=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)))


"Tami" wrote:

hi.
I need a formula that automatically sums down to the first blank cell.
In the data below i need a formula for the Total lines below...sometimes
there are 3 styles , sometimes 2 styles, sometimes 1 style. But i don't want
to have the user to have to adjust he formula every time as it will add risk
for human error.
any suggestions?

Total 450
Style A 100
Style B 200
Style C 150

Total 200
Style D 150
Style E 50

Total 250
Style F 250

Sum until hit blank
 

perfect, once again thank you ...

so now i had to put a lot of zeros in if there was no units so it woudn't
consider it a blank and stop the formula like this example

Total 450
Style A 100
Style B
Style C 150

had to change to this:

Total 450
Style A 100
Style B 0
Style C 150

so is there a way to format all zeros as "---"?





"Teethless mama" wrote:

=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)))


"Tami" wrote:

hi.
I need a formula that automatically sums down to the first blank cell.
In the data below i need a formula for the Total lines below...sometimes
there are 3 styles , sometimes 2 styles, sometimes 1 style. But i don't want
to have the user to have to adjust he formula every time as it will add risk
for human error.
any suggestions?

Total 450
Style A 100
Style B
Style C 150

Total 200
Style D 150
Style E 50

Total 250
Style F 250

Sum until hit blank
 

Teethless mama wrote...
=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)) )

Using a volatile function, so if there were many of these formulas
they could make EVERY recalc slow.

An alternative would be the array formula

=SUM(B2:INDEX(B2:B$65536,MATCH(FALSE,ISNUMBER(B2:B $65536),0)))

Sum until hit blank
 

i'm getting #n/a with your formula...i cut/pasted it and changed the 65000
number to 1000...what else could i try?

"Harlan Grove" wrote:

Teethless mama wrote...
=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)) )

Using a volatile function, so if there were many of these formulas
they could make EVERY recalc slow.

An alternative would be the array formula

=SUM(B2:INDEX(B2:B$65536,MATCH(FALSE,ISNUMBER(B2:B $65536),0)))

Sum until hit blank
 

I'm getting an #n/a...what else should i try?
thanks,

"Harlan Grove" wrote:

Teethless mama wrote...
=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)) )

Using a volatile function, so if there were many of these formulas
they could make EVERY recalc slow.

An alternative would be the array formula

=SUM(B2:INDEX(B2:B$65536,MATCH(FALSE,ISNUMBER(B2:B $65536),0)))

Sum until hit blank
 

Tami wrote...
I'm getting an #n/a...what else should i try?
....

It's an array formula. Did you hold down [Ctrl] and [Shift] keys
before pressing [Enter]?

It tested this with the following in B1:B21.


B1: 6 =SUM(B2:INDEX(B2:B$1000,MATCH(FALSE,ISNUMBER(B2:B$ 1000),0)))
B2: 1
B3: 2
B4: 3
B5:
B6: 22 =SUM(B7:INDEX(B7:B$1000,MATCH(FALSE,ISNUMBER(B7:B$ 1000),0)))
B7: 4
B8: 5
B9: 6
B10: 7
B11:
B12: 17 =SUM(B13:INDEX(B13:B$1000,MATCH(FALSE,ISNUMBER(B13 :B$1000),
0)))
B12: 8
B14: 9
B15:
B16: 60 =SUM(B17:INDEX(B17:B$1000,MATCH(FALSE,ISNUMBER(B17 :B$1000),
0)))
B17: 10
B18: 11
B19: 12
B20: 13
B21: 14


In each of the lines with a formula, the value at the beginning of the
line is produced by the formula, and all of these formulas are array
formulas, so entered with [Ctrl]+[Shift]+[Enter] rather than just
[Enter].

Sum until hit blank
 

are you saying that i would have to press control shift enter every time i
want it to calculate? if so, i'm thinking that that will be a problem with
the 50 people that will use this sheet...i just don't think i can't count on
100% compliance...any other ideas? is there anything risky with volatile
formulas? or just speed is compromised?

p.s thanks for helping me

"Harlan Grove" wrote:

Tami wrote...
I'm getting an #n/a...what else should i try?
....

It's an array formula. Did you hold down [Ctrl] and [Shift] keys
before pressing [Enter]?

It tested this with the following in B1:B21.


B1: 6 =SUM(B2:INDEX(B2:B$1000,MATCH(FALSE,ISNUMBER(B2:B$ 1000),0)))
B2: 1
B3: 2
B4: 3
B5:
B6: 22 =SUM(B7:INDEX(B7:B$1000,MATCH(FALSE,ISNUMBER(B7:B$ 1000),0)))
B7: 4
B8: 5
B9: 6
B10: 7
B11:
B12: 17 =SUM(B13:INDEX(B13:B$1000,MATCH(FALSE,ISNUMBER(B13 :B$1000),
0)))
B12: 8
B14: 9
B15:
B16: 60 =SUM(B17:INDEX(B17:B$1000,MATCH(FALSE,ISNUMBER(B17 :B$1000),
0)))
B17: 10
B18: 11
B19: 12
B20: 13
B21: 14


In each of the lines with a formula, the value at the beginning of the
line is produced by the formula, and all of these formulas are array
formulas, so entered with [Ctrl]+[Shift]+[Enter] rather than just
[Enter].

Sum until hit blank
 

No. You need to use Control Shift Enter when you insert or edit the
formula, but not when you merely want to enter new data and recalculate.
--
David Biddulph

Tami wrote:
are you saying that i would have to press control shift enter every
time i want it to calculate? if so, i'm thinking that that will be a
problem with the 50 people that will use this sheet...i just don't
think i can't count on 100% compliance...any other ideas? ...

p.s thanks for helping me

"Harlan Grove" wrote:

Tami wrote...
I'm getting an #n/a...what else should i try?
....

It's an array formula. Did you hold down [Ctrl] and [Shift] keys
before pressing [Enter]?

It tested this with the following in B1:B21.


B1: 6 =SUM(B2:INDEX(B2:B$1000,MATCH(FALSE,ISNUMBER(B2:B$ 1000),0)))
B2: 1
B3: 2
B4: 3
B5:
B6: 22 =SUM(B7:INDEX(B7:B$1000,MATCH(FALSE,ISNUMBER(B7:B$ 1000),0)))
B7: 4
B8: 5
B9: 6
B10: 7
B11:
B12: 17 =SUM(B13:INDEX(B13:B$1000,MATCH(FALSE,ISNUMBER(B13 :B$1000),
0)))
B12: 8
B14: 9
B15:
B16: 60 =SUM(B17:INDEX(B17:B$1000,MATCH(FALSE,ISNUMBER(B17 :B$1000),
0)))
B17: 10
B18: 11
B19: 12
B20: 13
B21: 14


In each of the lines with a formula, the value at the beginning of
the line is produced by the formula, and all of these formulas are
array formulas, so entered with [Ctrl]+[Shift]+[Enter] rather than
just [Enter].