Sum until hit blank

Tami · External Since: Aug 18, 2006 Posts: 22

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

Teethless mama · External Since: Sep 17, 2006 Posts: 1503

=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
>

Tami · External Since: Aug 18, 2006 Posts: 22

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

Harlan Grove · External Since: Apr 17, 2007 Posts: 120

Teethless mama <Teethlessm....DeleteThis@discussions.microsoft.com> 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)))

Tami · External Since: Aug 18, 2006 Posts: 22

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 <Teethlessm....DeleteThis@discussions.microsoft.com> 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)))
>

Tami · External Since: Aug 18, 2006 Posts: 22

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

"Harlan Grove" wrote:

> Teethless mama <Teethlessm....DeleteThis@discussions.microsoft.com> 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)))
>

Harlan Grove · External Since: Apr 17, 2007 Posts: 120

Tami <T....TakeThisOut@discussions.microsoft.com> 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].

Tami · External Since: Aug 18, 2006 Posts: 22

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 <T....TakeThisOut@discussions.microsoft.com> 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].
>

David Biddulph · External Since: Feb 24, 2007 Posts: 1283

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 <T... DeleteThis @discussions.microsoft.com> 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].