Combine PivotTable with prices to calculate billings by person

Question

I have the following: date, name, item, unit, quantity, deliverer, job in a pivot table using name and job in the row field; item in the column field and quantity as the value field.

My item's pricing differs from one item to another and also from one name to another, for which I created a table, for example:

          item 1  item 2  item 3
   Ethan:  34      45      54
   jack :  36      47      56  

What I want to do is to calculate each name's bill in a table based on the summary pivot table and the pricing chart. Any ideas how to do that?

Answer 1

You need to add the price into a column in your source data. If the "item" column is the same value as one of the item labels in the pricing chart and the "name" value is the same as in column 1 of the pricing chart, you can use a formula to look up the price for the transaction, for example

=index(PricingChart!$A$1:$D$100,match(B2,PricingChart!$A:$A,0),match(C2,PricingChart!$1:$1,0))

The above formula works if the pricing chart is on a sheet called PricingChart, starting in cell A1 (empty) and following the layout you show above. The formula is meant to be entered into the main data table, starting in row 2, assuming that the first row has column labels and the first date is displayed in cell A2.

Copy the formula down, label the column "price". Adjust the pivot table data source and you can use the price column for your calculations.

Answer 2

I’d suggest adding both price and cost (quantity * price) into your source data, line by line. Since your pivot table shows details by job and by item as well as by individual, for analysis purposes the following may be useful:

Though if say preparing invoices a layout (Classic style) as below may be more useful:

I have blacked out Sum of price totals as these are not meaningful.

@teylyn’s formula for looking up the prices does of course work but an alternative would be to name the row with items names say ‘items’ and the rest of your table ‘array’ and apply =VLOOKUP(B2,array,MATCH(C2,items,0),FALSE) (if your pricing table is in the same workbook as the rest of your data).