Calculate the sum by taking the relationship with the tables

[sysuserwindows]

Hi everyone,

I need to calculate the sum of a column quantity to Article material in Table 3 for each product material in Table 1, column A when Group in table 2 equals Group in table 3
Example: sum of (material article 2000) = 80+60+45+150 in table 3 (4 rows highlighted with color, excluding Group 20 and 21, because they don't exist in table 2) this is related to the three tables.

The formula in the Column issue quantity Column D for each Article material

 

[Domenic]

Try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($G$12:$G$27,MATCH(1,IF($H$12:$H$27=VLOOKUP(A2,$F$3:$G$6,2,0),IF($F$12:$F$27=B2,1)),0))

By the way, in future, please post your data as text or use the XL2BB add-in to do so instead of posting an image. This way people can easily copy and paste the data into their worksheet and work with it.

Hope this helps!

 

[sysuserwindows]

Try...

D2, confirmed with CONTROL+SHIFT+ENTER, and copied down:

=INDEX($G$12:$G$27,MATCH(1,IF($H$12:$H$27=VLOOKUP(A2,$F$3:$G$6,2,0),IF($F$12:$F$27=B2,1)),0))

By the way, in future, please post your data as text or use the XL2BB add-in to do so instead of posting an image. This way people can easily copy and paste the data into their worksheet and work with it.

Hope this helps!

Domenic, Thank you for your reply
but this formula looking at the value doesn't calculate the SUM of article material

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Product Material	Article Material	description			Product Material	Group
2	PR11	1000	Article 1	20		PR11	15
3	PR11	2000	Article 2	45		PR11	16
4	PR11	3000	Article 3	60		PR11	17
5	PR11	4000	Article 4	#N/A		PR12	18
6	PR11	5000	Article 5	50		PR12	19
7	PR12	2000	Article 6	80
8	PR12	6000	Article 7	100
9	PR12	7000	Article 8	70
10	PR12	9000	Article 9	55
11						Article Material	quantity	Group
12						1000	20	15
13						1000	15	15
14						1000	40	17
15						2000	80	18
16						3000	20	17
17						2000	60	17
18						5000	50	15
19						5000	70	15
20						2000	45	15
21						3000	60	15
22						6000	100	18
23						7000	70	18
24						9000	55	18
25						2000	150	19
26						2000	250	20
27						2000	30	21
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=B2+1000
D2:D10	D2	=INDEX($G$12:$G$27,MATCH(1,IF($H$12:$H$27=VLOOKUP(A2,$F$2:$G$6,2,0),IF($F$12:$F$27=B2,1)),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Domenic]

Sorry, my mistake. I now see that you did want the sum of quantity. In that case, try the following formula instead...

=SUM(IF($H$12:$H$27=VLOOKUP(A2,$F$2:$G$6,2,0),IF($F$12:$F$27=B2,$G$12:$G$27)))

Hope this helps!

 

[sysuserwindows]

Sorry, my mistake. I now see that you did want the sum of quantity. In that case, try the following formula instead...

=SUM(IF($H$12:$H$27=VLOOKUP(A2,$F$2:$G$6,2,0),IF($F$12:$F$27=B2,$G$12:$G$27)))

Hope this helps!

This formula calculates when the same Group , does not work with a different group for example, article material 2000 SUM = 80+60+45+150 with a different group in Table 3 (F12:H27) (groups existing in table 2 (F2:G6))

Book1.xlsx
	A	B	C	D	E	F	G	H
1	Product Material	Article Material	description	Issue quantity		Product Material	Group
2	PR11	1000	Article 1	35		PR11	15
3	PR11	2000	Article 2	45		PR11	16
4	PR11	3000	Article 3	60		PR11	17
5	PR11	4000	Article 4	0		PR12	18
6	PR11	5000	Article 5	120		PR12	19
7	PR12	2000	Article 6	80
8	PR12	6000	Article 7	100
9	PR12	7000	Article 8	70
10	PR12	9000	Article 9	55
11						Article Material	quantity	Group
12						1000	20	15
13						1000	15	15
14						1000	40	17
15						2000	80	18
16						3000	20	17
17						2000	60	17
18						5000	50	15
19						5000	70	15
20						2000	45	15
21						3000	60	15
22						6000	100	18
23						7000	70	18
24						9000	55	18
25						2000	150	19
26						2000	250	20
27						2000	30	21
Sheet1

Cell Formulas
Range	Formula
B3:B6	B3	=B2+1000
D2:D10	D2	=SUM(IF($H$12:$H$27=VLOOKUP(A2,$F$2:$G$6,2,0),IF($F$12:$F$27=B2,$G$12:$G$27)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Domenic]

Just to clear, can you please provide the actual expected results based on your sample data?

 

[Domenic]

It looks like this is probably what you want. If not, please provide the actual expected results based on your sample data.

sysuserwindows.xlsm
	A	B	C	D	E	F	G	H
1	Product Material	Article Material	description	Issue quantity		Product Material	Group
2	PR11	1000	Article 1	75		PR11	15
3	PR11	2000	Article 2	105		PR11	16
4	PR11	3000	Article 3	80		PR11	17
5	PR11	4000	Article 4	0		PR12	18
6	PR11	5000	Article 5	120		PR12	19
7	PR12	2000	Article 6	230
8	PR12	6000	Article 7	100
9	PR12	7000	Article 8	70
10	PR12	9000	Article 9	55
11						Article Material	quantity	Group
12						1000	20	15
13						1000	15	15
14						1000	40	17
15						2000	80	18
16						3000	20	17
17						2000	60	17
18						5000	50	15
19						5000	70	15
20						2000	45	15
21						3000	60	15
22						6000	100	18
23						7000	70	18
24						9000	55	18
25						2000	150	19
26						2000	250	20
27						2000	30	21
Sheet3

Cell Formulas
Range	Formula
B3:B6	B3	=B2+1000
D2:D10	D2	=SUM(IF(ISNUMBER(MATCH($H$12:$H$27,IF($F$2:$F$6=A2,$G$2:$G$6),0)),IF($F$12:$F$27=B2,$G$12:$G$27)))
Press CTRL+SHIFT+ENTER to enter array formulas.

Does this help?