Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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))) |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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]. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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]. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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]. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index/match - make blank cells return a blank value. | Excel Worksheet Functions | |||
Excel 2002: Return blank when VLOOKUP on blank cells | Excel Discussion (Misc queries) | |||
Average Formula to display blank cell if named range is blank | Excel Worksheet Functions | |||
where can I down Blank Worksheets, blank stmt. of account forms | Excel Discussion (Misc queries) | |||
Not showing blank and non blank items in filter mode for values | Excel Worksheet Functions |