Sum up table column values satisfying two row-conditions

Question

Below is my data in an Excel table:

+-------------+--------------+----------+
| Client Name | Sales Person | Net Sale |
+-------------+--------------+----------+
| ABC         | Jack         | 20,000   |
| BGS         | Sonia        | 38,888   |
| ABC         | Mira         |  6,744   |
| BGS         | Jack         | 32,000   |
| KNJ         | Mira         |  4,500   |
| ABC         | Jack         |  8,600   |
| KNJ         | Sam          | 56,000   |
+-------------+--------------+----------+

I would like to convert this data into the format:

+-------------+--------+--------+-------+---------+
| Client Name |  Jack  | Sonia  | Mira  |   Sam   |
+-------------+--------+--------+-------+---------+
| ABC         | 28,600 |        | 6,744 |         |
| BGS         | 32,000 | 38,888 |       |         |
| KNJ         |        |        | 4,500 | 56,000  |
+-------------+--------+--------+-------+---------+

The requirement is to generate a new table, crossing each row for Client Name with columns for Sales Person, and create a new table showing Net Sale values summing each Sales Person's values by each Client Name.

For example: in the case of Jack and ABC we have two sales, so we add the values and display the result: 28600 (sum of: 20,000 + 8,600).

Please help me out with formula, preferably without VBA.

Is your data in a string format which needs to be broken down? Please clarify. The result - is it expected to be a string or a table format? — Selfish
Client name Sales Person Net Sale ABC Jack 20,000 BGS Sonia 38,888 ABC Mira 6,744 BGS Jack 32,000 KNJ Mira 4,500 ABC Jack 8,600 KNJ Sam 56,000 — omkar patil
Have you ever used Pivot Tables? I think they might be helpful for your problem. — Tim Biegeleisen
Although this can be done with formulas, the use of Pivot Table as answered by @pnuts is more efficient and practical because the pivot table will get automatically updated for new Clients and Sales Persons, while with the formulas you will have to manually update and copy the formulas to extend the output to the range required. — EEM

pnuts pnuts · Accepted Answer · 2015-09-26T12:13:42

1

votes

This cries out for a PivotTable:

Sum up table column values satisfying two row-conditions

1 Answers