heretolearnexcel
Board Regular
- Joined
- Jan 22, 2019
- Messages
- 61
- Office Version
- 365
- Platform
- Windows
Hi,
I have a table of data that I turned into a pivot table. Here is an example of more or less the data I'm working with:
[TABLE="class: cms_table, width: 1000"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Type of movement[/TD]
[TD]Product A In
[/TD]
[TD]Product A Out[/TD]
[TD]Unit Cost[/TD]
[TD]Unit Cost[/TD]
[TD]Client[/TD]
[TD]Selling Price[/TD]
[TD]Product A in Stock[/TD]
[/TR]
[TR]
[TD]10/11/19[/TD]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]10/3/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Client A[/TD]
[TD]$25[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]9/23/19[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]9/22/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Client B[/TD]
[TD]$20[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]9/15/19[/TD]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]9/13/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Client D[/TD]
[TD]$18[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]9/10/19[/TD]
[TD]A[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9/02/19[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Client A[/TD]
[TD]$25[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]8/24/19[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
I turned the data into a pivot table and ended up with this:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Type of movement[/TD]
[TD]Sum of Product A in[/TD]
[TD]Sum of product A out[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]67[/TD]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]
I would like for the pivot table to display in the Grand Total row the difference between Sum of Product A in and Sum of Product A out, such that the result in the example would be =67-31= 36. I tried adding a formula next to the 31 which, supposing Type of movement= A1, would be in cell C9 and the 31 would be in D9. But if I filter for Type of movement and more values are added, changing the size of the pivot table, then the formula in D9 will no longer subtract the values from the grand total since they will no longer be in the original cells.
I have a table of data that I turned into a pivot table. Here is an example of more or less the data I'm working with:
[TABLE="class: cms_table, width: 1000"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Type of movement[/TD]
[TD]Product A In
[/TD]
[TD]Product A Out[/TD]
[TD]Unit Cost[/TD]
[TD]Unit Cost[/TD]
[TD]Client[/TD]
[TD]Selling Price[/TD]
[TD]Product A in Stock[/TD]
[/TR]
[TR]
[TD]10/11/19[/TD]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]10/3/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]10[/TD]
[TD][/TD]
[TD]2[/TD]
[TD]Client A[/TD]
[TD]$25[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]9/23/19[/TD]
[TD]C[/TD]
[TD]5[/TD]
[TD][/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]90[/TD]
[/TR]
[TR]
[TD]9/22/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]Client B[/TD]
[TD]$20[/TD]
[TD]91[/TD]
[/TR]
[TR]
[TD]9/15/19[/TD]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]70[/TD]
[/TR]
[TR]
[TD]9/13/19[/TD]
[TD]B[/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD]4[/TD]
[TD]Client D[/TD]
[TD]$18[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]9/10/19[/TD]
[TD]A[/TD]
[TD]7[/TD]
[TD][/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]100[/TD]
[/TR]
[TR]
[TD]9/02/19[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]8[/TD]
[TD][/TD]
[TD]3[/TD]
[TD]Client A[/TD]
[TD]$25[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]8/24/19[/TD]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
I turned the data into a pivot table and ended up with this:
[TABLE="class: cms_table, width: 500"]
<tbody>[TR]
[TD]Type of movement[/TD]
[TD]Sum of Product A in[/TD]
[TD]Sum of product A out[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD][/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]30[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]7[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]67[/TD]
[TD]31[/TD]
[/TR]
</tbody>[/TABLE]
I would like for the pivot table to display in the Grand Total row the difference between Sum of Product A in and Sum of Product A out, such that the result in the example would be =67-31= 36. I tried adding a formula next to the 31 which, supposing Type of movement= A1, would be in cell C9 and the 31 would be in D9. But if I filter for Type of movement and more values are added, changing the size of the pivot table, then the formula in D9 will no longer subtract the values from the grand total since they will no longer be in the original cells.