suming parts of a coloumn

[Roger Dodger]

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,

 

[Pete_UK]

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$22)-SUM(E$1:E1)-SUMIF
(C$2:C2,"<>",D$22),""),IF(C2<>"","",IF(OR(C3<>"",D3=""),SUM(D$22)-
SUM(E$1:E1)-SUMIF(C$2:C2,"<>",D$22),""))))

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

 

[pshepard]

=IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$22)-SUM(E$1:E1)-SUMIF(C$2:C2,"<>",D$22),""),IF(C2<>"","",IF(OR(C3<>"",D3=""),SUM(D$22)-SUM(E$1:E1),""))))

This formula works for me.

 

[Pete_UK]

Let's hope the OP thinks so, Peggy.

Pete

=IF(D2="","",IF(AND(C2="",C3=""),IF(D3="",SUM(D$22)-SUM(E$1:E1)-SUMIF(C$2­:C2,"<>",D$22),""),IF(C2<>"","",IF(OR(C3<>"",D3=""),SUM(D$22)-SUM(E$1:E­1),""))))

This formula works for me.
--
If this post helps click Yes
---------------
Peggy Shepard






- Show quoted text -

 

[Roger Dodger]

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



Let's hope the OP thinks so, Peggy.

Pete

 

[Pete_UK]

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$22)-SUM(E
$1:E1),""),IF(C2<>"",D2,IF(OR(C3<>"",D3=""),SUM(D$22)-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