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,

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,

=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,

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.

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 -

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 -