|
suming parts of a coloumn
Hello everyone,
I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. There is no regular pattern to the amount of numbers that it will need to add. Your help would be greatly appreciated. Roger, |
suming parts of a coloumn
I'm assuming your numbers start on row 2, so insert a blank row at the
top if you don't have a header row. Put this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF (C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C3<"" ,D3=""),SUM(D$2:D2)- SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),"")))) and copy down as far as you need to. Note this is all one formula - be wary of spurious line breaks which some newsreaders introduce. Hope this helps, Pete On Sep 7, 8:37*am, "Roger Dodger" wrote: Hello everyone, I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. There is no regular pattern to the amount of numbers that it will need to add. Your help would be greatly appreciated. Roger, |
suming parts of a coloumn
=IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C 3<"",D3=""),SUM(D$2:D2)-SUM(E$1:E1),"")))) This formula works for me. -- If this post helps click Yes --------------- Peggy Shepard "Pete_UK" wrote: I'm assuming your numbers start on row 2, so insert a blank row at the top if you don't have a header row. Put this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF (C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C3<"" ,D3=""),SUM(D$2:D2)- SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),"")))) and copy down as far as you need to. Note this is all one formula - be wary of spurious line breaks which some newsreaders introduce. Hope this helps, Pete On Sep 7, 8:37 am, "Roger Dodger" wrote: Hello everyone, I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. There is no regular pattern to the amount of numbers that it will need to add. Your help would be greatly appreciated. Roger, |
suming parts of a coloumn
Let's hope the OP thinks so, Peggy.
Pete On Sep 7, 10:33*am, pshepard (donotspam) wrote: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF(C$2*:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR( C3<"",D3=""),SUM(D$2:D2)-SUM(E$1:E*1),"")))) This formula works for me. -- If this post helps click Yes --------------- Peggy Shepard "Pete_UK" wrote: I'm assuming your numbers start on row 2, so insert a blank row at the top if you don't have a header row. Put this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF (C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C3<"" ,D3=""),SUM(D$2:D2)- SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),"")))) and copy down as far as you need to. Note this is all one formula - be wary of spurious line breaks which some newsreaders introduce. Hope this helps, Pete On Sep 7, 8:37 am, "Roger Dodger" wrote: Hello everyone, I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. |
suming parts of a coloumn
Hi Pete and Peggy, I tried the formula and it's very close. The problem is that it is suming the info in column D and totaling it with the line i don't want. This is what it needs to look like. CutNo Subs Description TOTmtrs 70 241 CREASES 11.3 11.3 71 65 65 72 995 SALES SAMPLE 0.3 0.3 73 54 119.3 74 264 SHADE BARS 9.2 9.2 75 65 76 65 77 65 78 65 79 65 80 66 81 998 REMNANT 4.1 4.1 82 65 83 65 84 65 85 65 86 65 87 65 390 88 998 REMNANT 1.3 1.3 89 65 90 66 91 65 196 92 998 REMNANT 1.6 1.6 93 65 94 66 95 65 96 65 261 97 264 SHADE BARS 9.9 9.9 98 39 39 99 244 SHADE VARY/TAILI 59.3 100 244 SHADE VARY/TAILI 13.7 73 Any further thoughts? Thankyou Roger "Pete_UK" wrote in message ... Let's hope the OP thinks so, Peggy. Pete On Sep 7, 10:33 am, pshepard (donotspam) wrote: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF(C$2*:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR( C3<"",D3=""),SUM(D$2:D2)-SUM(E$1:E*1),"")))) This formula works for me. -- If this post helps click Yes --------------- Peggy Shepard "Pete_UK" wrote: I'm assuming your numbers start on row 2, so insert a blank row at the top if you don't have a header row. Put this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF (C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C3<"" ,D3=""),SUM(D$2:D2)- SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),"")))) and copy down as far as you need to. Note this is all one formula - be wary of spurious line breaks which some newsreaders introduce. Hope this helps, Pete On Sep 7, 8:37 am, "Roger Dodger" wrote: Hello everyone, I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. There is no regular pattern to the amount of numbers that it will need to add. Your help would be greatly appreciated. Roger,- Hide quoted text - - Show quoted text - |
suming parts of a coloumn
Your layout is a bit difficult to follow, but it looks as if you want
the total to be reflected on the rows where there is an entry in column C. If that is the case, then use this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E $1:E1),""),IF(C2<"",D2,IF(OR(C3<"",D3=""),SUM(D$ 2:D2)-SUM(E $1:E1),"")))) It will give you this in column E (using your sample data): 11.3 65 0.3 54 9.2 391 4.1 390 1.3 196 1.6 261 9.9 39 59.3 13.7 Hope this helps. Pete On Sep 8, 8:11*am, "Roger Dodger" wrote: Hi Pete and Peggy, I tried the formula and it's very close. The problem is that it is suming the info in column D and totaling it with the line i don't want. This is what it needs to look like. * * * CutNo *Subs Description * * * TOTmtrs * * * 70 241 CREASES * * * * *11.3 11.3 * * * 71 * * * * * * * * * * * *65 65 * * * 72 995 SALES SAMPLE * * 0.3 0.3 * * * 73 * * * * * * * * * * * *54 119.3 * * * 74 264 SHADE BARS * * * 9.2 9.2 * * * 75 * * * *65 * * * 76 * * * *65 * * * 77 * * * *65 * * * 78 * * * *65 * * * 79 * * * *65 * * * 80 * * * *66 * * * 81 998 REMNANT * * * * *4.1 4.1 * * * 82 * * * *65 * * * 83 * * * *65 * * * 84 * * * *65 * * * 85 * * * *65 * * * 86 * * * *65 * * * 87 * * * *65 390 * * * 88 998 REMNANT * * * * *1.3 1.3 * * * 89 * * * *65 * * * 90 * * * *66 * * * 91 * * * *65 196 * * * 92 998 REMNANT * * * * *1.6 1.6 * * * 93 * * * * * * * * * * * *65 * * * 94 * * * * * * * * * * * *66 * * * 95 * * * * * * * * * * * *65 * * * 96 * * * * * * * * * * * *65 261 * * * 97 264 SHADE BARS * * * 9.9 9.9 * * * 98 * * * * * * * * * * * *39 39 * * * 99 244 SHADE VARY/TAILI 59.3 * * * 100 244 SHADE VARY/TAILI 13.7 73 Any further thoughts? Thankyou Roger "Pete_UK" wrote in message ... Let's hope the OP thinks so, Peggy. Pete On Sep 7, 10:33 am, pshepard (donotspam) wrote: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF(C$2**:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR (C3<"",D3=""),SUM(D$2:D2)-SUM(E$1:*E*1),"")))) This formula works for me. -- If this post helps click Yes --------------- Peggy Shepard "Pete_UK" wrote: I'm assuming your numbers start on row 2, so insert a blank row at the top if you don't have a header row. Put this formula in E2: =IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$2: D2)-SUM(E$1:E1)-SUMIF (C$2:C2,"<",D$2:D2),""),IF(C2<"","",IF(OR(C3<"" ,D3=""),SUM(D$2:D2)- SUM(E$1:E1)-SUMIF(C$2:C2,"<",D$2:D2),"")))) and copy down as far as you need to. Note this is all one formula - be wary of spurious line breaks which some newsreaders introduce. Hope this helps, Pete On Sep 7, 8:37 am, "Roger Dodger" wrote: Hello everyone, I am wondering if you guys can help me. I have tried my work colleagues and they can't get it either. I have 1 column of numbers, lets call it Column D. In this column there are a variety of different numbers. In Column C the only cells with input are not to be added at all. What I want to do is sum down column D until a cell has input beside it in C, then put a subtotal in column E and skip past the row with input in column C and start the process again down the column. There is no regular pattern to the amount of numbers that it will need to add. Your help would be greatly appreciated. Roger,- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
| All times are GMT +1. The time now is 07:52 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com