Another sub total question

  • Thread starter Thread starter durbat2
  • Start date Start date
D

durbat2

Sorry if this has been covered but I have a spreadsheet with shift A B
or C in col A. A part number in col B and the quantity of each part in
col C. Each shift can do a number of part numbers in a shift and what
I need is a formula in D which will add the number of parts produced
for each shift e.g. subtotal col C for every change in Col A.

A B C D
Shift Part No Qty shift total
a 123 23
a 23 45
a 21 13 81
b 323 45
b 23 23 68

Hope this makes sense

Thanks in anticipation of a solution
Martin
 
Hi,

A pivt table will give a nice professional report.

Select the data then
data|Pivot table and pivot chart report
select pivot table
Next
The data range should already be populated so click next
Select existing worksheet and a cell where you want the report
Finish

Drag "Shift' into the row area
Drag "Qty" into the data area

Close the pivot dialog and your done.

Mike
 
Thakns for the response Mike but I don't think I made my self clear,
this is a production log sheet so is added to every day. I need to be
able to look back and see the number of components completed by each
shift on different days so need a total for ABC on each date

A B C D
Shift Part No Qty shift total
a 123 23
a 23 45
a 21 13 81
b 323 45
b 23 23 68
c 1 47
c 323 23
c 23
c 232 25 118
a 23 13
a 2323 15 28
b 23 35
b 23 23
b 23 14 72
etc. etc.
 
Hi,

Put this in d2 and drag down

=IF(A2<>A3,SUM($C$2:C2)-SUM($D$1:D1),"")

Mike






- Show quoted text -
Click to expand...

That does it! thanks that will save me some time in future

Martin
 
Hi,

here is a different approach that does what you need:

=IF(A2=A3,"",SUMIF($A$2:$A$6,A2,$C$2:$C$6))

By the way, you could do what you want with a pivot table even as the data
change.
 
Hi,

Put this in d2 and drag down

=IF(A2<>A3,SUM($C$2:C2)-SUM($D$1:D1),"")

Mike
Click to expand...

Tested the solution out at work today and another problems cropped up,
the solution works fine totaling the production from each shift during
the same day but on occasions a shift might finish one day and start
on the same job on the following day. Is it possible to total the
quantity for either a change in shift or a change in date

MArtin
 
Back
Top