summing adjacent data in a worksheet
 

I have a column of data and I need to sum the values in adjacent cells when
they are bounded before and after by cells that contain a value of 0. Then I
need to sum the next group of cells bounded by zeros. So I want a column with
subtotals rather than one total. The number of cells in one group may vary.
I've tried using the sumif function, but what I want doesn't seem to quite
fit.

I'm using Excel 2007.

summing adjacent data in a worksheet
 

So, you have something like this:

1
2
3
0
4
5
6
7
0
0
8
9
0
10
11
12
13
0
0

and you want to get something like this:

1
2
3 6
0
4
5
6
7 22
0
0
8
9 17
0
10
11
12
13 46
0
0

Well, I put that sample data in column L starting with L2, and then
put this formula in M2:

=IF(AND(L3=0,L2<0),SUM(L$1:L2)-SUM(M$1:M1),"")

and just copied it down to get the above result.

Hope this helps.

Pete


On Nov 26, 10:15*pm, Suzie wrote:
I have a column of data and I need to sum the values in adjacent cells when
they are bounded before and after by cells that contain a value of 0. Then I
need to sum the next group of cells bounded by zeros. So I want a column with
subtotals rather than one total. The number of cells in one group may vary.
I've tried using the sumif function, but what I want doesn't seem to quite
fit.

I'm using Excel 2007.

summing adjacent data in a worksheet
 

Thanks a lot. That works well.
Cheers,
Suzie

"Pete_UK" wrote:

So, you have something like this:

1
2
3
0
4
5
6
7
0
0
8
9
0
10
11
12
13
0
0

and you want to get something like this:

1
2
3 6
0
4
5
6
7 22
0
0
8
9 17
0
10
11
12
13 46
0
0

Well, I put that sample data in column L starting with L2, and then
put this formula in M2:

=IF(AND(L3=0,L2<0),SUM(L$1:L2)-SUM(M$1:M1),"")

and just copied it down to get the above result.

Hope this helps.

Pete


On Nov 26, 10:15 pm, Suzie wrote:
I have a column of data and I need to sum the values in adjacent cells when
they are bounded before and after by cells that contain a value of 0. Then I
need to sum the next group of cells bounded by zeros. So I want a column with
subtotals rather than one total. The number of cells in one group may vary.
I've tried using the sumif function, but what I want doesn't seem to quite
fit.

I'm using Excel 2007.

.

summing adjacent data in a worksheet
 

You're welcome, Suzie - thanks for feeding back.

Pete

On Nov 30, 10:46*pm, Suzie wrote:
Thanks a lot. That works well.
Cheers,
Suzie



"Pete_UK" wrote:
So, you have something like this:

1
2
3
0
4
5
6
7
0
0
8
9
0
10
11
12
13
0
0

and you want to get something like this:

1
2
3 * * * 6
0
4
5
6
7 * * *22
0
0
8
9 * * * 17
0
10
11
12
13 * * *46
0
0

Well, I put that sample data in column L starting with L2, and then
put this formula in M2:

=IF(AND(L3=0,L2<0),SUM(L$1:L2)-SUM(M$1:M1),"")

and just copied it down to get the above result.

Hope this helps.

Pete

On Nov 26, 10:15 pm, Suzie wrote:
I have a column of data and I need to sum the values in adjacent cells when
they are bounded before and after by cells that contain a value of 0. Then I
need to sum the next group of cells bounded by zeros. So I want a column with
subtotals rather than one total. The number of cells in one group may vary.
I've tried using the sumif function, but what I want doesn't seem to quite
fit.

I'm using Excel 2007.

.- Hide quoted text -

- Show quoted text -