summing adjacent data in a worksheet

  • Thread starter Thread starter Suzie
  • Start date Start date
S

Suzie

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.
 
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
 
Thanks a lot. That works well.
Cheers,
Suzie

Pete_UK said:
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




.
Click to expand...
 
Back
Top