Hi.
I have the following table which tracks payments to various employees over multiple invoices. I've organised it so each row represents a particular job with one column tabulating the total cost of the entire job. I want to create new columns at the end of each row which capture the total for each employee for that given job. As an example, consider the following table:
| Job | P1 | Amount | P2 | Amount | P3 | Amount | P4 | Amount | Job Total |
| ABC | Jay | $100 | Jay | $200 | Mary | $200 | Mary | $50 | $550 |
| DEF | Bill | $250 | Mary | $300 | Mary | $200 | Bill | $1,000 | $1,750 |
In the above case, I want to add a column for Jay, a column for Mary, and a column for Bill that identifies their contribution to the job. That is, I want something like the following:
| Job | P1 | Amount | P2 | Amount | P3 | Amount | P4 | Amount | Job Total | Jay Total | Mary Total | Bill Total |
| ABC | Jay | $100 | Jay | $200 | Mary | $200 | Mary | $50 | $550 | $300 | $250 | $0 |
| DEF | Bill | $250 | Mary | $300 | Mary | $200 | Bill | $1,000 | $1,750 | $0 | $500 | $1,250 |
I've tried using a PivotTable and SUMIF/SUMIFS, but both approaches haven't worked for me thus far. Any ideas on how to approach this?
