Group Data on Columns in Pivot Table

I have a table that looks like this:

Job Agent1 Comm1 Agent2 Comm2
1 Bob $100 Dave $300
2 Dave $200 Joe $200
3 Joe $200 Bob $100
4 Dave $200 Joe $500
5 Bob $500 NA NA


I would like to summarize it with a pivot table as such:

Agent Total Comm
Bob $700
Dave $700
Joe $900

Is this possible? Thanks for the help!

Thanks for the response! The original data cannot be manipulated as it is pulling directly from a database. If I do it this way it looks like it creates a new table that is not connected to the original data. Therefore if the original data is changed it won't update the flattened summary table.

If you are querying from DB. Can you not write native SQL query with Unpivot operator to flatten table?

The only thing I am pulling from the database is the Job and Agent 1. The rest is entered into the spreadsheet in the format I posted originally. We are doing this as the sales system is limited in its ability to track any commissions. Therefore I created this tool so that they can, however I would like to summarize the data for them.

Hmm, you could do it with formula. But I wouldn't recommend that route, as it can slow down performance.

Another option to do it using VBA, if you are open to it.

Other than that, PowerQuery/Get&Transform is your best option (or generate report using some other tool, Ex: PowerBI Pro etc).