Formula to add up rows until the blank cell?

[tonywatsonhelp]

Hi Everyone,

Thought this would be easy so maybe my brains not working today but need your help

I have amounts in column G
these amounts are broken up using spaces

so I would like a formula that I can put into row G that will add all the data above it until it comes to a space (Blank cell)
how can I do this?

thanks

Tony
as in example below, even after I have added the formula there will always be at least 1 blank cell befre the numbers start again.
Example
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]Column G
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]10
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]I Need formula here rows 2:4
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]I Need formula here rows 7:12
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]18
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]19
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]21
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]22
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]23
[/TD]
[TD]I Need formula here rows 16:22
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]24
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]26[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]28
[/TD]
[TD]11
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]29
[/TD]
[TD]I Need formula here rows 27:28
[/TD]
[TD]<strike></strike>
[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hello,

In cell H2, you can test following Array formula:

=IF(AND(G2="",G3=""),IFERROR(1/(1/SUM(G2:INDEX(G$1:G1,MATCH(9.99999999999999E+307,1/(1-ISNUMBER(G$1:G1)))))),""),"")

Use simultaneously the three keys Control+Shift+Enter ... instead of the Enter key ...

HTH

 

[Nishant94]

Unknown
	G
1
2	10
3	11
4	11
5	32
6
7	11
8	11
9	11
10	11
11	11
12	11
13	66
14
15
16	11
17	11
18	11
19	11
20	11
21	11
22	11
23	77
24
25
26
27	11
28	11
29	22
Sheet4

Cell Formulas
Range	Formula
G5	{=SUM(INDEX(G$1:G4,LARGE(IF(G$1:G4="",ROW(G$1:G4)-ROW($G$1)+1),1)):G4)}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Nishant94]

A non-array formula:

Unknown
	G
1
2	10
3	11
4	11
5	32
6
7	11
8	11
9	11
10	11
11	11
12	11
13	66
14
15
16	11
17	11
18	11
19	11
20	11
21	11
22	11
23	77
24
25
26
27	11
28	11
29	22
Sheet4

Cell Formulas
Range	Formula
G5	=SUM(INDEX(G$1:G4,LARGE(INDEX((G$1:G4="")*(ROW(G$1:G4)-ROW(G$1)+1),0),1)):G4)

 

[James006]

@tonywatsonhelp,

How do you plan to have formulas located along the data ... within the same column ...

Is your data structure dynamic with blanks rows potentially appearing anywhere ...or static ...as currently shown ...?