Formula to add up rows until the blank cell?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
3,210
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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]
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
Hello,

In cell H2, you can test following Array formula:

Code:
=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
 
Upvote 0

Unknown
G
1
210
311
411
532
6
711
811
911
1011
1111
1211
1366
14
15
1611
1711
1811
1911
2011
2111
2211
2377
24
25
26
2711
2811
2922
Sheet4
Cell Formulas
RangeFormula
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.
 
Last edited:
Upvote 0
A non-array formula:


Unknown
G
1
210
311
411
532
6
711
811
911
1011
1111
1211
1366
14
15
1611
1711
1811
1911
2011
2111
2211
2377
24
25
26
2711
2811
2922
Sheet4
Cell Formulas
RangeFormula
G5=SUM(INDEX(G$1:G4,LARGE(INDEX((G$1:G4="")*(ROW(G$1:G4)-ROW(G$1)+1),0),1)):G4)
 
Last edited:
Upvote 0
@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 ...?
 
Upvote 0

Forum statistics

Threads
1,223,248
Messages
6,171,021
Members
452,374
Latest member
keccles

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top