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

" wrote:

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

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

" wrote:

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.

On Nov 9, 4:36*pm, Mike H wrote:
Hi,

Put this in d2 and drag down

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

Mike



" wrote:
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.- Hide quoted text -

- Show quoted text -

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.


--
Thanks,
Shane Devenshire


" wrote:

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,

Have you tried using Data Subtotal.

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

" wrote in message
...
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

On Nov 9, 4:36*pm, Mike H wrote:
Hi,

Put this in d2 and drag down

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

Mike



" wrote:
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.- Hide quoted text -


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