If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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,L20),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. |
#3
|
|||
|
|||
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,L20),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. . |
#4
|
|||
|
|||
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,L20),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 - |
Thread Tools | |
Display Modes | |
|
|