Sheet 1
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>
Sheet 2
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>
<colgroup><col width="64" style="width: 48pt;" span="8">
<tbody>
</tbody>
Client | Sales | Returns |
A01 | ||
A02 |
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>
Sheet 2
Product | Client | Sales | Product | Client | Sales |
Blue | A01 | 51 | Red | A02 | 32 |
Blue | A02 | 895 | Red | A01 | 947 |
Blue | A01 | 256 | Red | A02 | 367 |
Blue | A01-5 | 226 | Red | A02 | 458 |
Blue | A02 | 1005 | Red | A01 | 2568 |
Blue | A02 | 3 | Red | A01-5 | 66 |
Blue | A01-5 | 626 | Red | A02 | 749 |
Blue | A02 | 189 | Red | A01-5 | 301 |
<colgroup><col width="64" style="width: 48pt;" span="3"><tbody>
</tbody>
In sheet 1 I want to sum sales of products by client, so I need my sum formula to: | ||
(a) look in col B and E (sheet 2) to find the client (they never change and always contain text) | ||
(b) treat clients A01 and A01-5 as if they are the same (row 1 Sheet 1) Any suggestions for a solution much appreciated. |