# SumProduct or INDEX MATCH and help using it

#### jamieleeuk

##### Board Regular
Hi,

I've not posted for a long time, but now I am need help again and my first port of call was Mr Excel.

I have two tables on the same sheet

 A B C D E F G H I J batch # weight in Batch num weight out AA0001 10,000 AA0001 3,000 AA0002 7,000 AA0001 2,000 AA0003 6,000 AA0002 4,000 AA0004 12,000 AA0003 2,000 AA0003 3,000 AA0003 1,000 AA0001 1,000 AA0004 7,000

<tbody>
</tbody>

I have a dropdown list in cell H1 which lists the unique Batch numbers the left hand table and in cell H2, I want the following to happen:

Say for instance I chose AA0001 in the drop down box, in cell H2, I would like excel to add up all the 'weight' values for batch number AA0001 in the left hand table and then deduct the sum of values with the corresponding Batch numbers in the right hand table.

So, if I chose AA0001 from the drop down list, H2 would give me the answer of 4,000 (being the 10,000 we received in, less the 3,000 plus 2,000 plus 1,000 that we have sent out).

For the life of me I cannot get the hang over Index Match or Sumproduct and I'm sure you guys will be able to tell me in the blink of an eye!

Thanks for any assistance,
Jamie

Last edited:

### Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

##### MrExcel MVP
H1: Aa0001

H2, just enter:

=SUMIFS(B:B,A:A,H1)-SUMIFS(F:F,E:E,H1)

#### jamieleeuk

##### Board Regular
thumbs up!

classic overthinking!

##### MrExcel MVP
thumbs up!

classic overthinking!

Can happen. Glad to see that the problem is solved.

#### Hercules1946

##### Well-known Member
I'll post mine, as it works as well, and I only realised it was solved when I completed it:

In H2:
=SUMIF(A:A,H1,B:B)-SUMIF(E:E,H1,F:F)

Replies
2
Views
322
Replies
3
Views
722
Replies
6
Views
424
Replies
10
Views
547
Replies
2
Views
278

1,195,970
Messages
6,012,609
Members
441,715
Latest member
TTP

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back