I want to run a formula to look up a contract ref and run sum values for supplier invoices depending on their supply category as eg below - please advise formula - I reckon combo of vlookup, sumif, sumproduct???
<tbody>
</tbody>
Input | Output | |||||||||
Customer Contract Ref | Invoice Ref | Value | Supplier | Fruits | Veg | Customer Contract Ref | Fruits | Veg | ||
10001 | 1 | 5000 | Supplier A | Supplier A | Supplier B | 10001 | 7000 | 300 | ||
10001 | 2 | 300 | Supplier B | Supplier C | | 10002 | 7000 | 400 | ||
10001 | 3 | 2000 | Supplier C | Supplier D | | 10003 | 1300 | 600 | ||
10002 | 4 | 400 | Supplier B | |||||||
10002 | 5 | 7000 | Supplier A | |||||||
10003 | 6 | 300 | Supplier A | |||||||
10003 | 7 | 100 | Supplier A | |||||||
10003 | 8 | 900 | Supplier D | |||||||
10003 | 9 | 600 | Supplier B |
<tbody>
</tbody>