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.
0
Utf
11/26/2009 10:15:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

3 Replies
690 Views

Similar Articles

[PageSpeed] 38

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:

=3DIF(AND(L3=3D0,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=A0pm, Suzie <Su...@discussions.microsoft.com> wrote:
> I have a column of data and I need to sum the values in adjacent cells wh=
en
> they are bounded before and after by cells that contain a value of 0. The=
n 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 var=
y.
> I've tried using the sumif function, but what I want doesn't seem to quit=
e
> fit.
>
> I'm using Excel 2007.

0
Pete_UK
11/26/2009 11:57:31 PM
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 <Su...@discussions.microsoft.com> 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.
> 
> .
> 
0
Utf
11/30/2009 10:46:01 PM
You're welcome, Suzie - thanks for feeding back.

Pete

On Nov 30, 10:46=A0pm, Suzie <Su...@discussions.microsoft.com> 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 =A0 =A0 =A0 6
> > 0
> > 4
> > 5
> > 6
> > 7 =A0 =A0 =A022
> > 0
> > 0
> > 8
> > 9 =A0 =A0 =A0 17
> > 0
> > 10
> > 11
> > 12
> > 13 =A0 =A0 =A046
> > 0
> > 0
>
> > Well, I put that sample data in column L starting with L2, and then
> > put this formula in M2:
>
> > =3DIF(AND(L3=3D0,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 <Su...@discussions.microsoft.com> wrote:
> > > I have a column of data and I need to sum the values in adjacent cell=
s 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 col=
umn 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 -

0
Pete_UK
12/1/2009 1:17:58 AM
Reply:

Similar Artilces: