Sum Difference products in difference tables

[molittlemo]

Dear All..
Thanks for your view in advance.

I got 2 table of products:
For example:
Table 1:
Product No. Quantity
101 10
102 20
103 30
Table 2: (Its the movement of products)
101 -1
101 -2
101 5
102 1
102 4
104 35

My purpose is to sum the balance of products 101,102,103,104
My question is:
(1). I think I should sum up table 2 first,, so the result should be:
Table 2:
101 2
102 5
104 3
How should I do it?

Then sum up table 1 and 2, so the result should be:
101 12
102 25
103 30
104 35

This can be done by vlookup, but as product 103 does appear in table 2, and product 104 does not appear in table 1.
Again how should I do it?

Thanks so much~~

[molittlemo]

I have solved the first part.
So now the problem is, I got 2 table
1 is 2 is
Product No. Quantity Product No. Quantity
101 10 101 2
102 5 102 20
104 3 103 30

How do I set a formula so that the result would be:
101 12
102 25
103 30
104 5

[johnmpl]

Hi, molittlemo!

Next time you should upload a sample Excel workbook showing us what you have and what you want to obtain.

As per your post #2, If you have the first table in A1:D4, and second table in F2:G5 (putting you manually the Product No. 101 - 102 - 103 - 104), try this formula in G2:
=SUMIF(A$2:C$4,F2,B$2:D$4)

And drag it down. Blessings!

[FlameRetired]

The results agree with your first post ... not your second. I believe the first one is correct.

	A	B	C	D	E	F	G	H	I
1	Table1			Table2			101	12	=SUMIF(A:A,G1,B:B)+SUMIF(D:D,G1,E:E)
2	ProductNo.	Quantity		ProductNo.	Quantity		102	25
3	101	10		101	-1		103	30
4	102	20		101	-2		104	35
5	103	30		101	5
6				102	1
7				102	4
8				104	35