Help with Excel

Question

I am trying to configure a spreadsheet to calculate payments to subcontractors. Subcontractors work at several locations each week and we need to know the cost for each of the locations and a total to pay. We also need to be able to filter the data by 2 fields to give us lists to pay subcontractors, dependant upon the contract terms.

I have a spreadsheet set up already, but currently have to put the locations in the same cell to be able to filter the data as needed. I insert a pivot table to analyse the data, but if a subcontractor works on multiple locations in the same week, I have to do a manual calculation to calculate the cost. This is what we are trying to avoid.