Use Pivot Table to calculate 2 field columns from different files

[fordtsai]

Need help on how to calculate 2 different fields from different worksheets and get total sum of the quantity.
example. I'm using excel 2013, i have DAY and NIGHT shift data which I wanted to merged into 1 full day shift with grand total of the quantity based on machine / date respectively. Is this possible using Pivot Table?
here is the link of the file for reference:
https://onedrive.live.com/redir?res...465&authkey=!AML6Ttn34AYA5cM&ithint=file,xlsx

Day Shift
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Machine No.[/TD]
[TD]Commodity[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]UFP Housing[/TD]
[TD]1040[/TD]
[TD]3 Jan[/TD]
[/TR]
[TR]
[TD]B14[/TD]
[TD]Spyglass[/TD]
[TD]15600[/TD]
[TD]4 jan[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Hubble[/TD]
[TD]15060[/TD]
[TD]4 Jan[/TD]
[/TR]
</tbody>[/TABLE]








Night Shift
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Machine No.[/TD]
[TD]Commodity[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Hubble[/TD]
[TD]14000[/TD]
[TD]4 Jan[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Air Spoiler[/TD]
[TD]8840[/TD]
[TD]4 jan[/TD]
[/TR]
[TR]
[TD]A7[/TD]
[TD]MFP[/TD]
[TD]13550[/TD]
[TD]4 Jan[/TD]
[/TR]
</tbody>[/TABLE]








This should be the result when two files combined:
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Machine No.[/TD]
[TD]Commodity[/TD]
[TD]Quantity[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]C1[/TD]
[TD]UFP Housing[/TD]
[TD]1040[/TD]
[TD]3 Jan[/TD]
[/TR]
[TR]
[TD]B14[/TD]
[TD]Spyglass[/TD]
[TD]15600[/TD]
[TD]4 Jan[/TD]
[/TR]
[TR]
[TD]A3[/TD]
[TD]Hubble[/TD]
[TD]29060[/TD]
[TD]4 Jan[/TD]
[/TR]
[TR]
[TD]A5[/TD]
[TD]Air Spoiler[/TD]
[TD]8840[/TD]
[TD]4 Jan[/TD]
[/TR]
</tbody>[/TABLE]









adding machine A3 with the same date.

please help as i don't know how to do this one.
thanks.

 

[CyrusTheVirus]

You could try messing around with consolidating multiple tables with a pivot table by pressing Alt, D, P, then click 'Multiple consolidation ranges', etc...

OR you could try creating a new sheet that would use a SUMIFS + SUMIFS formula. Do this by creating a new worksheet then adding headers A1 'Machine No.' B1 'Commodity' C1 'Quantity' D1 'Date'. Then put A2 as 'A3', B2 as 'Hubble', C2 as formula

=SUMIFS('2016-Day_Shift_Prod'!$E$2:$E$324,'2016-Day_Shift_Prod'!$A$2:$A$324,Sheet3!A4,'2016-Day_Shift_Prod'!$S$2:$S$324,Sheet3!D4)+SUMIFS('2016-Night_Shift_Prod'!$E$2:$E$324,'2016-Night_Shift_Prod'!$A$2:$A$324,Sheet3!A4,'2016-Night_Shift_Prod'!$Q$2:$Q$324,Sheet3!D4)

and D2 as the date '4-Jan'