Sum values in a column until the next empty cell

[Waldemar]

Hi, I can't figure out a formula to sum in a column all values below, until, say an empty cell appears.
I generate the sheet where the formula appears, I don't know on which row the formula appears and how many values below to sum up..

___A__
1| ???
2| 2
3| 3
4| 5
5|
6| 4
7| 3
8| 2

The result of A1 should be 10

Any help available? Thanx!
Waldemar

 

[fairwinds]

Hi,

Try:

=SUM(A2:INDEX(A2:A100,MATCH(TRUE,(A2:A100=""),0)))

confirmed with Ctrl + shift + enter.

 

[Waldemar]

Thank you, this works.. but in my original sheet I need to do this a couple of times in the same column, so I can't use the array from A2:A100
I guess I had to be more explicit, sorry

____A__ B_____
01| 1 |??? < =10
02| . | 2
03| . | 3
04| . | 5
05| . |
06| 1 | ??? < = 9
07| . | 4
08| . | 3
09| . | 2
10| . |
11| 1 |??? < = 3
12| . | 1
13| . | 2
14| . |

The cell values are inserted from another worksheet, say WORK!
The formula will be IF(A1=1 ; (count until..) ; WORK!A1)

I hoped to find a formula on B1, saying 'SUM all values, only until the next empty cell appears'
This is exactly what you solution does, but for B1 the array should be calculated as 'the next 3 cells', for B6 also, and B11 for the next 2 cells.

Regards,
Waldemar

 

[fairwinds]

I think I might be missing something but if you enter the formula in E.g. B1 and the copy it to the other cells you want it in, should that not work?

Just don't forget Ctrl + shift + enter.

 

[Waldemar]

I enterered the formula with ctrl+shift+enter and it does what it should do, but the a formula includes all the outcomes below.

In my example, in which I added another column to make it a one-sheet test environment, this happens:

__|A__|_B_|_C_
01| 1 | . | 40 < should be 10
02| . | 2 | 2
03| . | 3 | 3
04| . | 5 | 5
05| . | . | 0
06| 1 | . | 15 < should be 9
07| . | 4 | 4
08| . | 3 | 3
09| . | 2 | 2
10| . | . | 0
11| 1 | . | 3 < should be 3
12| . | 1 | 1
13| . | 2 | 2
14| . | . | 0

The formula for C1 =
{=IF(A1=1;SUM(C2:INDEX(C2:C100;MATCH(TRUE;(C2:C100="");0)));B1)}

Note-1: In my case the formula only works with ; instead of a comma
Note-2: Cell C5 = 0, but in real it refers to a formula result ""

 

[fairwinds]

=IF(A2=1,SUM(B2:INDEX(B2:$B$100,MATCH(TRUE,(B3:$B$100=""),0))),B2)

is entered with Ctrl + shift + enter in C2 and dragged down

Book2
	A	B	C	D
1
2	1		10
3		2	2
4		3	3
5		5	5
6			0
7	1		9
8		4	4
9		3	3
10		2	2
11			0
12	1		3
13		1	1
14		2	2
Sheet7

 

[Waldemar]

OK! Done!
Its a bit different approach then I was looking for. Now I'll sum directly on the sourcedata on another worksheet (in the example represented by column B)

I thought the solution should be something like this:
in C1: IF A1=1, then search in column C for the next empty cell, calculate how many cells are between the current cell and the empty cell, sum the cells next to the current cell until the empty cell.
Dragging this C1 down to C2, the first condition IF A1=1 would be false, so no calculation there.

At the end the result is what counts
Thank you very much!

Waldemar

 

[jcmcdonald]

Any help with this?

I need to add cells until the next cell (counting down)(going up) is empty or equals zero.


This picture should sum up my question.


No pun intended.


Thanks