calculate sum in between 2 empty cell

[Guest]

Hi Everyone,

I have a column of data, they are all 1s and some empty cell in between
them. i want to fill the empty cell with the sum of all the 1s until the next
empty cell.

Is it possible?

many Thx

 

[VBA Noob]

You could select the column and Data > Sub totals


VBA Noob

 

[Leo Heuser]

Hi Everyone,

I have a column of data, they are all 1s and some empty cell in between
them. i want to fill the empty cell with the sum of all the 1s until the
next
empty cell.

Is it possible?

many Thx

Hi Rick

Assuming data in A1 and down.

One way:

1. Select column A (click in the column name)
2. Choose <F5> and click "Special" (or similar)
3. Select "Empty cells" and OK.
4. Scroll down (empty cells still selected!), so the cell below the last
data cell is visible
5. Hold <Ctrl> while clicking that cell. Release <Ctrl>.
6. Click the "Autosum" icon on the toolbar
7. Clear superfluous cells with sums below (if any).

 

[Guest]

Hi Leo,

It is really impressive!!

Thank U again

 

[Leo Heuser]

Hi Leo,

It is really impressive!!

Thank U again

You're welcome, Rick, and thanks for the feedback.

Leo Heuser

 

[Guest]

Hi, Leo,

I don't know why, the method only worked once for me. now every time i
tried after step 6, the '=SUM()' goes to the top cell and doen't do the
trick!?

 

[Guest]

Yes, The first cell can not be empty. can be solved by add a '0'.

 

[Leo Heuser]

Hi, Leo,

I don't know why, the method only worked once for me. now every time i
tried after step 6, the '=SUM()' goes to the top cell and doen't do the
trick!?

Hi again

You probably have one or more empty cells
at the start of the column.
If for instance your first data cell is A5, instead
of step 1 do this:

In the name box (at the extreme left of the formula bar)
enter A5:A10000 (10000 can be any number >=
the address of the last cell in column A containinig data)
Finish with <Enter>.

Continue with step 2 to 6.

Leo Heuser