How to Return/Sum Multiple Values in Excel while disragarding Duplicates

[douglasjoh]

Hello!

First time Posting,

I've been given a new project at work where I need to look up customers and come back with the customers multiple contract numbers in one cell. As well as Summing their total order quantities in these contracts (of the same product) in a separate cell - While Disregarding Duplicates in the data. I realise this is quite complicated, but is there anyway this is possible? I have been using Index/Match but this brings back the first value it finds.

Please see below an example of the sort of data I have to retrieve these answers from:


Thank you!
[TABLE="width: 214"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Contract[/TD]
[TD]Quantity[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A1[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A4[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A6[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A7[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD]A8[/TD]
[TD="align: right"]15[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A5[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A10[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Bill[/TD]
[TD]A12[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A3[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A15[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]John[/TD]
[TD]A16[/TD]
[TD="align: right"]40[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A2[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]A18[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]A13[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Julie[/TD]
[TD]A20[/TD]
[TD="align: right"]15[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[Eric W]

Did you enter the formula as an array formula? To make sure, select G5, press F2 to start editing the formula, remove the {} on the ends if they are there, then press Control+Shift+Enter instead of just Enter to enter the formula. That tells Excel that it's an array formula, and if you did it right, Excel will add the {} on the ends for you.