Combine Data from 2 Pivot Tables into a Third Pivot Table (Excel for Mac)

Question

I have two tables of data that I would like to be able to view in a pivot table. The first table is things I have produced, the second table is things I have sold. I would like a pivot table that shows what inventory I have remaining. I set up pivot tables for each table, that are set-up in a way that is easy for me to view, and I'd like the third to be set-up with the same layout. PivotTable Fields Screenshot

Production Table The sales table uses the same structure, minus the ink colour, and including a Place column.

At the moment, I have sheets for each different kind of item I print on, i.e. bags / t-shirts / womens t-shirts / onesies / etc which are set up like this:

Current Tables that I would like to replace with pivot tables

Answer 1

In your table i suppose the unique identifier for each product is product and color columns combinantion (Note that if you provide a column as ID you can distinguish same products with different colors, but the approach is the same). So we need count each produced product and also count each sold product Then subtract their values. So in inventory sheet, we have product name and product color columns (and the other optional columns). we insert two new columns (Note that we can just create one column, but for better undrestanding we create two), one for count of produced products and the other for sold product. for counting each product based on name and color, we must use COUNTIFS formula. Suppose we have product names in column F and colors in column B of produced sheet. If we want to count red p1 products, the formula in H2 cell of inventory sheet whould be:

=COUNTIFS(produced!F:F,F2,produced!B:B,B2)

this formula means: in produced table, if procuct is P1 and color is RED count it. We drag down this formula to other cells. simillary for count sold products the formula (for I2) whould be:

=COUNTIFS(sold!F:F,F2,sold!B:B,B2)

by subtracting these two cells we would have count of remaining red p1 product.