Sum until hit blank

  • Thread starter Thread starter Tami
  • Start date Start date
T

Tami

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

had to change to this:

so is there a way to format all zeros as "---"?
 
Teethless mama said:
=SUMPRODUCT(OFFSET(B2,,,MATCH(TRUE,B2:B1000="",)))
Click to expand...

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)))
 
i'm getting #n/a with your formula...i cut/pasted it and changed the 65000
number to 1000...what else could i try?
 
Tami said:
I'm getting an #n/a...what else should i try?
Click to expand...
....

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].
 
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 said:
Tami said:
I'm getting an #n/a...what else should i try?
Click to expand...
....

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].
Click to expand...
 
Back
Top